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:

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:

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 = ++;

Db. Database. ExecuteSqlCommand (SQL );

The MySQL official document is written as follows:

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 =
+;

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;