熟悉 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 导入初始化数据, 便会导致数据不一直或初始化失败
来源: http://www.linuxidc.com/Linux/2018-02/150786.htm