How to Prevent auto-Growth of primary keys when data insertion in mysql fails

Source: Internet
Author: User
Tags mysql insert
Mysql fails to insert data to prevent auto-increment primary key growth method mysql sets the auto-increment primary key ID, and the auto-increment ID that fails to insert is also added, for example, five failed, the next success is not to add 1 to the previous successful data, but to add 6 to the data. The number of failures increases by 1 at a time. Can you keep the failed data from increasing? Or mysql insert

Mysql fails to insert data to prevent auto-increment primary key growth method mysql sets the auto-increment primary key ID, and the auto-increment ID that fails to insert is also added, for example, five failed, the next success is not to add 1 to the previous successful data, but to add 6 to the data. The number of failures increases by 1 at a time. Can you keep the failed data from increasing? Or mysql insert

How to Prevent auto-Growth of primary keys when data insertion in mysql fails

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.

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

When a Mysql primary key is inserted, values are not controlled using auto-incrementing programs.
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 increases by 6, and the number of failures increases by 1 at a time. Can you allow programs to control failure without increasing the number of failures?

Method/step
1
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 });
You can use the program to control the input ID.

From Mysql primary key insertion, values will not be controlled using auto-incrementing programs _ Baidu experience
Http://jingyan.baidu.com/article/48206aeae55922216ad6b384.html

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.