In Chapter 9's Allowing individual INSERT statements with "0" values in auto-incrementing columns recipe, the NO_AUTO_VALUE_ON_ZERO option to the SQL_MODE system variable was used. To fully understand what was happening here, we suggest you to follow along on a little experiment.
Follow the preparations described in Allowing individual INSERT statements with "0" values in auto-incrementing columns (Chapter 9). Once you are done, connect to a test database and drop a possibly existing enumerator table (as used in the recipe mentioned above).
Create the database schema afresh:
mysql> DROP TABLE IF EXISTS enumerator; mysql> CREATE TABLE enumerator ( id INT NOT NULL AUTO_INCREMENT, textvalue VARCHAR(30), PRIMARY KEY (id) ) ENGINE=InnoDB;Try to insert and read back some data like this:
mysql> INSERT INTO enumerator VALUES (0,'Zero'),(1,'One'), (2,'Two'),(3,'Three'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'See if anything was actually inserted:
mysql> SELECT * FROM enumerator; Empty set (0.00 sec)
Obviously nothing happened, as was to be expected because of the error message we got.
Try the exact same
INSERTstatement again to increase confusion:mysql> INSERT INTO enumerator VALUES (0,'Zero'),(1,'One'), (2,'Two'),(3,'Three'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM enumerator;
When the table was just created, its auto-increment value was reset to be 1 for the first record to be inserted. When we tried to insert the first batch of records shown previously, the (0,'Zero')
record was actually interpreted as a request to assign a new auto-increment value for the id column. As this was going to be the first record, MySQL actually tried to insert a (1,'Zero') record.
However, the second record we tried to insert as part of our statement was (1,'One'). This conflicted with the id value that had just been generated for the first row, making the overall INSERT statement fail.
Even though no records were inserted, MySQL increased the internal counter for this table's auto-increment value once for each record we tried to insert. Issuing the same INSERT again will work after that because the first record (0,'Zero') is now translated to an actual (5,'Zero'), not creating a conflict for the other values.
Note
This behavior can cause subtle errors if you do not notice what is going on right away because you might be working on data different from what you expect! This is one of the reasons why in general it is considered bad practice to insert your own values for auto-incrementing columns; so strive to avoid it if possible!
You can define the behavior shown here for a single session as the default behavior for a MySQL server. See the Globally allowing INSERT statements with "0" values in auto-incrementing columns recipe in Chapter 9 for more details on how to do that.
For more information on the SQL_MODE variable and its various settings, refer to the online manual, section 5.1.8 Server SQL Modes at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html.