MySQL教程-當AUTO_INCREMENT自增遇到0

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匯入初始化資料，便會導致資料不一直或初始化失敗。