10

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.

Mehran
  • 15,593
  • 27
  • 122
  • 221

1 Answers1

10

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)
Mchl
  • 61,444
  • 9
  • 118
  • 120
  • 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! – Mehran Aug 01 '11 at 08:43
  • 1
    Mehran Ziadloo: I just did a check to be sure, and the IDs were sequential. – Mchl Aug 01 '11 at 08:44
  • My bad, using `IGNORE` modifier will not increment the counter. That is for rows which _duplicate key_ errors are turned into warnings. – Mehran Aug 01 '11 at 08:47