MySQL tutorial-When AUTO_INCREMENT auto-increment encounters 0

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:

CREATE TABLE `tb1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

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

INSERT INTO tb1 VALUES(1,1);

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:

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

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

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.