MySQL tutorial-When AUTO_INCREMENT auto-increment encounters 0

Source: Internet
Author: User
Tags mysql tutorial

MySQL tutorial-When AUTO_INCREMENT auto-increment encounters 0

DBAs familiar with SQL Server know that if you want to display the auto-increment values in SQL Server, except for the auto-increment columns in the INSERT statement, you also need to use SET IDENTITY_INSERT TB_NAME ON to tell the system "I want to display the insert auto-increment", but in MySQL, it becomes "free" to display the insert auto-increment ".

Assume that the table tb1 is defined as follows:

  `c1` int(11) DEFAULT NULL, PRIMARY KEY (`id`)

To display the inserted auto-increment, you can use:


Of course, it is okay to write the column:

INSERT INTO tb1(id,c1)VALUES(2,2);

There is no problem in setting the auto-increment value of the inserted negative value:

INSERT INTO tb1(id,c1)VALUES(-1,-1);

Then insert 0?

INSERT INTO tb1(id,c1)VALUES(0,0); 

Although the auto-increment value is 0, the auto-increment value is 1. What does this mean?

I checked the MySQL help document and found that this problem is related to a variable: NO_AUTO_VALUE_ON_ZERO. Generally, when a record is inserted, if the auto-incrementing column is NULL or 0, the system will obtain a new auto-increment value for the auto-increment value of this insert. If you want to use 0 as the auto-increment value, you need to set the SQL MODE to NO_AUTO_VALUE_ON_ZERO, for example:


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

The official documentation is as follows:

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.
This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way .) for example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when
It encounters the 0 values, resulting in a table with contents different from the one that was dumped. enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically provided des in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

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

Although you can use NO_AUTO_VALUE_ON_ZERO to set the auto-increment value to 0, we do not recommend that you set the auto-increment value to 0 to avoid data differences during data migration or synchronization due to different environment settings, if NO_AUTO_VALUE_ON_ZERO is not set in the slave database, importing the initialization data through mysqldump will result in data interruption or initialization failure.

Related Article

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: 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.