MySQL solution for inserting a value of 0 into a self increment column
- 2020-05-15 02:22:16
- OfStack
During the migration of the database from MSSQL to MySQL, a value of 0 is inserted in the self-increment column of MySQL, as required by the business logic. This is done in MSSQL:
The official MySQL documentation reads:
NO_AUTO_VALUE_ON_ZERO will affect the self-increment column. Generally, the way to get the next sequence value is to insert 0 or NULL value into the self-increment column. NO_AUTO_VALUE_ON_ZERO changes this default behavior so that the next sequence value can only be obtained by inserting the NULL value. This is useful for inserting a value of 0 into a self-increment column. For example, if you use mysqldump to back up a data table and then restore it, MySQL1 will automatically generate a new sequence value with a value of 0, resulting in an error in restoring data from a backup. Before recovering the data, enabling NO_AUTO_VALUE_ON_ZERO can solve this problem. mysqldump will now automatically include NO_AUTO_VALUE_ON_ZERO in the output statement to solve this problem.
This is required in MySQL:
Problem solved.
Postscript:
In order to copy data between Windows platform and MySQL of Linux platform and add global variable Settings, add NO_AUTO_VALUE_ON_ZERO to sql-mode line respectively in my.ini and my.cnf, for example:
After restarting the MySQL service, see the sql-mode global variable method:
SELECT @@GLOBAL.sql_mode;
> > > > > There is no copyright > > > > > Welcome to reprint > > > > > The original address > > > > > http://www.cnblogs.com/jlzhou > > > > > When the eagle grows up in the henhouse, he will lose his ability to fly. When the Wolf grows up in the flock, he will fall in love with the sheep and lose his Wolf nature. The secret of life is to get along with people. The beauty of life is to give roses. When you are with a wise person, you will become wiser. Only with the best people can you stand out. So it doesn't matter who you are, it matters who you are with.
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);
The official MySQL documentation reads:
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 will affect the self-increment column. Generally, the way to get the next sequence value is to insert 0 or NULL value into the self-increment column. NO_AUTO_VALUE_ON_ZERO changes this default behavior so that the next sequence value can only be obtained by inserting the NULL value. This is useful for inserting a value of 0 into a self-increment column. For example, if you use mysqldump to back up a data table and then restore it, MySQL1 will automatically generate a new sequence value with a value of 0, resulting in an error in restoring data from a backup. Before recovering the data, enabling NO_AUTO_VALUE_ON_ZERO can solve this problem. mysqldump will now automatically include NO_AUTO_VALUE_ON_ZERO in the output statement to solve this problem.
This is required in 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) ";
Problem solved.
Postscript:
In order to copy data between Windows platform and MySQL of Linux platform and add global variable Settings, add NO_AUTO_VALUE_ON_ZERO to sql-mode line respectively in my.ini and my.cnf, for example:
//my.ini The file in Windows7 or Windows2008 In the operating system C:\ProgramData\MySQL\MySQL Server 5.6 Table of contents (adopted MSI Installation method) # Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO"
After restarting the MySQL service, see the sql-mode global variable method:
SELECT @@GLOBAL.sql_mode;
> > > > > There is no copyright > > > > > Welcome to reprint > > > > > The original address > > > > > http://www.cnblogs.com/jlzhou > > > > > When the eagle grows up in the henhouse, he will lose his ability to fly. When the Wolf grows up in the flock, he will fall in love with the sheep and lose his Wolf nature. The secret of life is to get along with people. The beauty of life is to give roses. When you are with a wise person, you will become wiser. Only with the best people can you stand out. So it doesn't matter who you are, it matters who you are with.