db renumbering

This method involves discarding unnecessary data and starting from the latest data. In essence, it is a minor adjustment to the numbering system. There are rare cases where verification is required. Careful attention is required when operating the command line (SQL) as it directly affects the database (incorrect operation is not permitted).

ALTER TABLE tbl_a AUTO_INCREMENT = 16;

This is for when you want to delete numbers 16 and above from the database and start numbering again from 16. If you don’t know the latest number, you can write it like this.

SELECT MAX(id) AS max_id_a FROM tbl_a;

The above is a method for obtaining the current maximum value from the latest ID.

max_id_a：31

If 31 is returned, add “+1” as shown below and execute with 32.

ALTER TABLE tbl_a AUTO_INCREMENT = 32;

It is also acceptable to add “+1” to the current maximum value as shown below.

SELECT IFNULL(MAX(id) + 1, 1) AS next_id FROM tbl_a;

This is a measure to prevent unnecessary empty slots. Adjusting values in anticipation of future numbering may facilitate data management. Verification is required.

MySQL/MariaDB compatible. Other RDBs (e.g. PostgreSQL) need conversion.