Repeated issues after the MySQL auto-incrementing primary key is deleted _ MySQL

Duplicate MySQL auto-incrementing primary key after deletion problem bitsCN.com

Repeated issues after the MySQL auto-incrementing primary key is deleted

This is a problem with a friend of my colleague. The title may be a bit closed. I will reproduce the following scenario:

Set a MySQL table with an auto-incrementing primary key ID. Insert data to the table. if the ID of the last row of the table is 100 after the data is inserted, delete the record with ID 100 first, then restart the server. it is reasonable to say that if a new record is inserted into this table, the ID of the new record will be 101, right? It is estimated that everyone thinks so. However, the result is: if your table type is InnoDB, the ID of the new record is 100. if your table type is MyISAM, the ID of the new record is 101. However, if we delete the record with ID 100 but do not restart the server, a new record will be added, and the auto-increment ID of the new record will be 101.

Why does this happen?

This is because different table types have different auto-increment IDs: InnoDB data tables store the last ID value of the table in the memory. Therefore, when we restart the server and clear the data in the memory, the auto-increment ID is calculated based on the record of the existing table. On the contrary, if it is a MyISAM data table, keep the maximum record ID in the file. in this way, although the server is restarted, the next time you insert a new record, the auto-increment ID is calculated by reading the file.

How can this problem be solved?

I believe everyone can come up with the following method: do not restart the server. However, in some cases, you have to restart the server, because the server has to be restarted for a long time. Some may also say that setting the table structure to the MyISAM type is feasible as long as it does not affect the existing business. Here, I will share my method: My method is very simple. In fact, there is no need to delete those records. in many cases, the table auto-increment ID we set does not make much sense. Therefore, we can add a new field in the table structure to indicate whether the record is valid, such as the status field. 1 indicates that the record is valid, 0 indicates that the record is invalid. if you do not want to see any records, you only need to set the status field value of these records to 0. The new record ID will be automatically added as scheduled.

BitsCN.com