MySQL 自增列插入0值的方法_MySQL

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 whenit 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) ";

至此问题解决。

 

 

 

bitsCN.com