Home  >  Article  >  Database  >  MySQL自增列插入0值的解决方案_MySQL

MySQL自增列插入0值的解决方案_MySQL

WBOY
WBOYOriginal
2016-06-01 13:23:58825browse

bitsCN.com 在将数据库从MSSQL迁移到MySQL的过程中,基于业务逻辑的要求,需要在MySQL的自增列插入0值。在MSSQL中是这样完成的:

string sql;sql = " set identity_insert dbo.AppUsers on "
+ " insert dbo.AppUsers (Id, IsLocked, IsMustChangeLocalPassword, IsAvailable, Name, Sequence, CreatedBy, CreatedTime, UpdatedBy, UpdatedTime) "
+ " values (0, 1, 0, 0, '[SYSTEM]', 0, 0, GetDate(), 0, GetDate()) "
+ " set identity_insert dbo.AppUsers off "
+ " DBCC CHECKIDENT ('dbo.AppUsers', RESEED, 0) ";
db.Database.ExecuteSqlCommand(sql);

MySQL官方文档中是这样写的

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或者NULL值。NO_AUTO_VALUE_ON_ZERO会改变这个缺省的行为,使得只有插入NULL值才能获取下一个序列值。这种方式对于要将0值插入自增列是有用的。(顺便指出,0值是不推荐使用在自增列的)例如,如果你使用mysqldump备份数据表然后再恢复它,MySQL一般情形下会0值自动产生新的序列值,结果是造成从备份恢复数据错误。在恢复数据前,启用NO_AUTO_VALUE_ON_ZERO可以解决这个问题。mysqldump现在会自动在输出的语句中包含NO_AUTO_VALUE_ON_ZERO来解决这个问题。
在MySQL中需要这样

sql = " SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'; insert AppUsers (Id, IsLocked, IsMustChangeLocalPassword, IsAvailable, Name, Sequence, CreatedBy, CreatedTime, UpdatedBy, UpdatedTime) "
+ " values (0, 1, 0, 0, '[SYSTEM]', 0, 0, CURRENT_TIMESTAMP, 0, CURRENT_TIMESTAMP) ";

至此问题解决。
后记
由于业务逻辑需要,在自增列会存在0值,为了在Windows平台和Linux平台的MySQL之间复制数据,增加全局变量设置,在my.ini和my.cnf中分别添加NO_AUTO_VALUE_ON_ZERO设置到sql-mode行,例如:

//my.ini 该文件在Windows7或Windows2008操作系统中位于 C:/ProgramData/MySQL/MySQL Server 5.6 目录下(采用MSI安装方式)# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"

重启MySQL服务后,查看sql-mode全局变量的办法
SELECT @@GLOBAL.sql_mode;
>>>>> 版权没有 >>>>> 欢迎转载 >>>>> 原文地址 >>>>> http://www.cnblogs.com/jlzhou >>>>> 雄鹰在鸡窝里长大,就会失去飞翔的本领,野狼在羊群里成长,也会爱上羊而丧失狼性。人生的奥妙就在于与人相处。生活的美好则在于送人玫瑰。和聪明的人在一起,你才会更加睿智。和优秀的人在一起,你才会出类拔萃。所以,你是谁并不重要,重要的是,你和谁在一起。bitsCN.com

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn