Mysql Data insertion failure to prevent auto-growth primary key growth method, mysql primary key

Mysql Data insertion failure to prevent auto-growth primary key growth method, mysql primary key

Mysql sets the ID of the auto-increment primary key, and the auto-increment ID that fails to be inserted is also added. For example, if five failed keys are added, the next successful step is not to add 1 to the data that was successfully inserted, instead, it is directly increased by 6, and the number of failures increases by 1 at a time. Can we keep the failure from increasing?

Or mysql fails to insert data. How can we prevent the growth of primary keys?

MYSQL does not guarantee that AUTO_INCREMENT increases sequentially (, 5), but it can guarantee positive growth (,). Therefore, when an operation fails, the next AUTO_INCREMENT is not sequential.

Innodb auto-increment is cached in the memory dictionary. The allocation method is reserved first and then inserted. Therefore, if insertion fails, the memory dictionary will not be rolled back. To enable innodb to identify the current maximum id, restart the server to update the AUTO_INCREMENT cache, or use the alter table 'table 'AUTO_INCREMENT = maximum number;

This idea is basically not practical. The following describes a convenient method.

If a Mysql primary key has a value during insert, auto-increment is not used. That is to say, when inserting data, you only need to add the ID by yourself, and then perform auto-increment according to the number of inserts. This number is assigned by your own logic judgment code, as long as the ID is unique. For example:

String SQL = "insert ignore into mytable (id, number, opendate) values (?,?,?) ";

New Object [] {id, number, opendate });

Another way to ensure this is to query the maximum ID before each insert, and then add 1 to insert as the ID.

Of course, the temporary table method is also used to ensure that the final insertion is successful, but it is quite troublesome.