MySQL auto-increment column inserts 0 values

When migrating a database from MSSQL to MySQL, You need to insert 0 values in the auto-increment column of MySQL based on the requirements of business logic. In MSSQL, this is done as follows:

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 MySQL official document is written as follows:

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 provided des in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

Generally, NO_AUTO_VALUE_ON_ZERO will affect the auto-incrementing column. Generally, the method to obtain the next sequence value is to insert 0 or NULL values to the auto-incrementing column. NO_AUTO_VALUE_ON_ZERO will change this default behavior so that only NULL values can be inserted to obtain the next sequence value. This method is useful for inserting 0 values into the auto-incrementing column. (By The Way, 0 values are not recommended for auto-incrementing columns.) For example, if you use mysqldump to back up the data table and restore it, in general, MySQL automatically generates a new sequence value with a value of 0, resulting in an error in data recovery from backup. Enable NO_AUTO_VALUE_ON_ZERO Before restoring data. Mysqldump will now automatically include NO_AUTO_VALUE_ON_ZERO in the output statement to solve this problem.
 
In MySQL, You need:

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

Now the problem is solved.

Postscript:

As the business logic requires, there will be a value of 0 in the auto-increment column. To copy data between MySQL on Windows and Linux, add global variable settings in my. ini and my. add NO_AUTO_VALUE_ON_ZERO in cnf to the SQL-mode line, for example:

// My. ini this file is located in the C: \ ProgramData \ MySQL Server 5.6 directory in Windows 7 or Windows (Using MSI installation) # Set the SQL mode to strict

SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, NO_AUTO_VALUE_ON_ZERO"

After the MySQL service is restarted, you can view the global variables of SQL-mode:

SELECT @ GLOBAL. SQL _mode;