快捷搜索:  as  test  1111  test aNd 8=8  test++aNd+8=8  as++aNd+8=8  as aNd 8=8

AG亚游手机版app:MySQL自增列插入0值的实现方法



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

string sql;

sql = " set identity_insert dbo.AppUsers on "+ " insert dbo.AppUsers (Id, IsLocked, IsMustChangeLocalPassword, IsAvailable, Name, Sequence, CreatedAG亚游手机版appBy, 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_OAG亚游手机版appN_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,AG亚游手机版app 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.AG亚游手机版appini 该文件在Windows7或Windows2008操作系统中位于 C:\ProgramData\MySQL\MySQL Server 5.6AG亚游手机版app 目录下(采纳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/archive/2013/03/18/2965384.html

免责声明:以上内容源自网络,版权归原作者所有,如有侵犯您的原创版权请告知,我们将尽快删除相关内容。

您可能还会对下面的文章感兴趣: