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