MySQL's AUTO_INCREMENT behavior in a multiple row insert

Question

I think the answer to my question is obvious but since I could not find any documentation to support it, I thought it's worth asking. At least for the record.

As we all know AUTO_INCREMENT fields are incremented each time an INSERT statement is executed. And its value can be retrieved by LAST_INSERT_ID() function. It is also mentioned in MySQL's Manual that with multiple-row inserts, LAST_INSERT_ID() will return the first ID of the inserted rows. Which I think is a good idea (really useful).

So here goes my question:

Can I assume in an INSERT IGNORE INTO statement with multiple-rows, the inserted IDs of an AUTO_INCREMENT field will always be sequential? Keep in mind that due to IGNORE modifier and the multi-user nature of MySQL server, different scenarios might happen.

Thanks.

Soubise

Answer 1

No you can not assume that. One scenario where ID's would not be sequential is in replicated multi-master setup. If for example two servers exist in such setup, one will only generate even auto IDs, and the other only odd IDs (keep in mind it's just an example).

However if your setup is not something like that, then yes. At least in InnoDB inserts are atomic and are queued when targetting same table, so ID's from two different INSERT's will not interlace. (it's not documented though, so relying on it is... a bit risky)

---

How I tested IGNORE INSERT ID generation:

mysql> CREATE TABLE  `ignoreinsert` (
    ->   `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `uq` int(10) unsigned NOT NULL,
    ->   PRIMARY KEY (`ID`),
    ->   UNIQUE KEY `uq` (`uq`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.19 sec)

mysql> INSERT INTO ignoreinsert VALUES (null,1),(null,2);
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ignoreinsert;
+----+----+
| ID | uq |
+----+----+
|  1 |  1 |
|  2 |  2 |
+----+----+
2 rows in set (0.00 sec)

mysql> INSERT IGNORE INTO ignoreinsert VALUES (null,3),(null,1),(null,4),(null,2),(null,5);
Query OK, 3 rows affected (0.08 sec)
Records: 5  Duplicates: 2  Warnings: 0

mysql> SELECT * FROM ignoreinsert;
+----+----+
| ID | uq |
+----+----+
|  1 |  1 |
|  2 |  2 |
|  3 |  3 |
|  4 |  4 |
|  5 |  5 |
+----+----+
5 rows in set (0.00 sec)

Poling

How about IGNORE modifier, do the duplicate rows advance the counter even though they are inserted? I ask this because I've seen that in single row INSERT statements without IGNORE modifier, the counter is incremented when some error happens, even though the row is not inserted! – Soubise

Mehran Ziadloo: I just did a check to be sure, and the IDs were sequential. – Poling

My bad, using IGNORE modifier will not increment the counter. That is for rows which duplicate key errors are turned into warnings. – Soubise