MySQL --当AUTO_INCREMENT自增遇到0

熟悉SQL Server的DBA都知道，在SQL Server中如果想要显示制定自增值，除了在INSERT语句中显示写明自增列外，还需要使用SET IDENTITY_INSERT TB_NAME ON来告诉系统“我要显示插入自增啦”，但到了MySQL，显示插入自增就变得很“自由”。

假设有表tb1，其定义如下：

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

如果需要显示插入自增，可以使用：

INSERT INTO tb1 VALUES(1,1);

当然写明列肯定是没问题的：

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

设置插入负值的自增值也没有问题：

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

那插入0呢？

 

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

 

虽然显示插入自增值为0,但是自增值变成了1，这是啥意思呢？

查看了下MySQL帮助文档，发现该问题和一个变量有关：NO_AUTO_VALUE_ON_ZERO，一般情况下，在插入记录时，如果自增列对于的数据为NULL或0时，系统会获取一个新的自增值来作为本次插入的自增值使用，如果希望将0作为自增值来使用，那些需要设置SQL MODE 为NO_AUTO_VALUE_ON_ZERO，如：

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

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

官方文档如下：

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 includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

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

尽管可以通过NO_AUTO_VALUE_ON_ZERO来将自增值设置为0，但不建议将自增值设置为0，以避免在数据迁移或同步时因环境设置不同而导致数据差异，如从库未设置NO_AUTO_VALUE_ON_ZERO的情况下通过mysqldump导入初始化数据，便会导致数据不一直或初始化失败。

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