MySQL Insert data failed method to prevent self-growing primary key growth

MySQL Insert data failed method to prevent self-growing primary key growth

MySQL set the self-growth of the primary key ID, insert the failure of the self-growth ID also added one, such as Failure 5, the next success is not in the original last success data plus 1, but directly into add 6, the number of failures once automatically increased by 1, can let the failure of the non-growth?

Or if MySQL fails to insert data, how can I prevent the primary key from growing?

MySQL does not guarantee that auto_increment will grow sequentially (1,2,3,4,5), but it can guarantee positive growth (1,3,5,9) So, when you fail a certain operation, the next auto_increment is not sequential.

The InnoDB is cached in the memory dictionary, and is allocated by first reserving and then inserting. Therefore, insert failure does not roll back the memory dictionary. The way to let InnoDB recognize the current maximum ID is to restart the server update auto_increment cache, or use ALTER TABLE ' table ' auto_increment = maximum number;

This idea is basically not practical, the following is a convenient method.

MySQL PRIMARY key If there is a value when insert is inserted, the self-increment is not used. That is, when inserting data, as long as you add the ID by the number of inserts to self-increment, this number through their own logic to determine the code to assign value, as long as the ID is not repeated. For example:

String sql = "Insert ignore into mytable (id,number,opendate) VALUES (?,?,?)";

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

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

Of course, there are temporary tables to ensure that the final insertion is successful, but it is more troublesome.

===============================================================

MySQL primary key inserted when there is a value will not use the self-increment available program control MySQL set the self-growth primary key ID, insert the failure of the self-growth ID also added one, such as Failure 5, the next success is not in the original last success data plus 1, but directly into add 6, the number of failures automatically increased by 1, Can the failure of the non-growth of the program to control free?

Method/Step 1 MySQL primary key if there is a value when insert is inserted, the self-increment is not used. That is, when inserting data, as long as you add the ID by the number of inserts to self-increment, this number through their own logic to determine the code to assign value, as long as the ID is not repeated. For example: String sql = "Insert ignore into mytable (id,number,opendate) VALUES (?,?,?)"; New object[] {id,number,opendate}); The ID entered with the program control can be freely controlled.