MySQL--When auto_increment self-increase encounters 0

DBAs familiar with SQL Server know that in SQL Server, if you want to display self-increment, you need to use SET IDENTITY_INSERT tb_name on to tell the system, in addition to displaying the add-on column in the INSERT statement, "I'm going to show the insert self-increment "But to MySQL, the show insertion increment becomes very" free ".

Suppose there is a table tb1, which is defined as follows:

CREATE TABLE' tb1 ' (' ID ')int( One) not NULLauto_increment, ' C1 'int( One)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=1 DEFAULTCHARSET=Utf8

If you need to display insert self-increment, you can use:

INSERT  into VALUES (1,1);

Of course the column must be no problem:

INSERT  into TB1 (ID,C1)VALUES(2,2);

There is no problem with setting the self-increment for inserting negative values:

INSERT  into TB1 (ID,C1)VALUES(-1,-1);

What about inserting 0?

INSERT  into TB1 (ID,C1)VALUES(0,0);

Although the display insertion self-increment is 0, but the self-increment becomes 1, what does that mean?

Viewed the following MySQL help document, found that the problem is related to a variable: No_auto_value_on_zero, in general, when inserting a record, if the self-increment column for the data is null or 0 o'clock, the system will obtain a new self-increment to use as the self-increment of this insertion, If you want to use 0 as self-increment, those who need to set SQL MODE to No_auto_value_on_zero, such as:

SET SESSION Sql_mode='no_auto_value_on_zero';

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

Official documents are as follows:

No_auto_value_on_zero affects handling of auto_increment columns. Normally, generate the next sequence number for the column is inserting either NULL or 0 into it. No_auto_value_on_zero suppresses this behavior for 0 so, only NULL generates the next sequence number.
This mode can be useful if 0 have been stored in a table ' s auto_increment column. (Storing 0 is a recommended practice.) 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 and was dumped. Enabling No_auto_value_on_zero before reloading the dump file solves this problem. Mysqldump now automatically includes in it output a statement that enables No_auto_value_on_zero, to avoid this problem.

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

Although you can set the self-increment value to 0 through No_auto_value_on_zero, it is not recommended to set the self-increment value to 0 to avoid data differences due to different environment settings when data is migrated or synchronized, such as from libraries not set No_auto_value_on_ Zero, importing initialization data through mysqldump results in data not being initialized or failed.

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

MySQL--When auto_increment self-increase encounters 0