What is auto_increment fields values order for MySQL INSERT .. SELECT statement

Question

Lets say we have next table:

CREATE TABLE test_insert_order (
  id INT(11) NOT NULL AUTO_INCREMENT,
  parent_id INT(11) NOT NULL,
  name VARCHAR(20),
  PRIMARY KEY (id)
);

With some data like

INSERT INTO test_insert_order (parent_id, name) VALUES (1, 'a'),(1, 'b'),(1,'c'),(2,'b'),(2,'d'),(2,'a'),(3,'d'),(3,'a'),(4,'aa'),(5,'bb'),(6,'a'),(3,'a'),(1,'d'),(2,'c');

If we do

INSERT INTO test_insert_order (parent_id, name) SELECT 7, `name` FROM test_insert_order WHERE parent_id = 2 ORDER BY id;

Can we assume that new auto_generated ids will be in the same order as ids in result of select

SELECT id, 7, `name` FROM test_insert_order WHERE parent_id = 2 ORDER BY id;

So in result next query a.name will always match b.name

SET @i:=0;set @j:=0;
SELECT a.id, a.parent_id, a.name, a.order_id, b.id, b.parent_id, b.name, b.order_id FROM
  (SELECT *, @j:=@j+1 as order_id FROM test_insert_order WHERE parent_id = 2 ORDER BY id) a,
  (SELECT *, @i:=@i+1 as order_id FROM test_insert_order WHERE parent_id = 7 ORDER BY id) b
WHERE a.order_id = b.order_id;

I have made a few tests with concurrent treads and it is always true. But I can not find anything in MySQL docs about this situation.

UPDATE: I guess this could be not true in some cluster solutions when a few instances have own pattern for autoincrement value, and one is lagging behind and query execution get distributed between then some how. But I do not have environment to check this.

Based on [this accepted answer](https://stackoverflow.com/questions/1950315/row-order-when-inserting-multplie-rows-in-mysql) the order of rows inserted matches the order of rows encountered in the select query. — Tim Biegeleisen, Oct 29 '19 at 14:56
@TimBiegeleisen So you are saying that ``` INSERT .. SELECT .. ORDER BY...; ``` is equivalent of ``` INSERT ... SELECT .... ORDER BY ... LIMIT 0, 1; INSERT ... SELECT .... ORDER BY ... LIMIT 1,1 ; ... INSERT ... SELECT .... ORDER BY ... LIMIT n-1,1 ; ``` Do you have any notes about that from MySQL docs? — Vo_Vik, Oct 29 '19 at 15:06
I don't know of a doc reference, but I have never known anything else to be true. — Tim Biegeleisen, Oct 29 '19 at 15:09
@TimBiegeleisen I just worry, because I have found many stack overflow answers stating that order by does not matter for INSERT ... SELECT statement, but non of them was about auto_increment field. Any way thank you for your comment. — Vo_Vik, Oct 29 '19 at 15:18
Yes, it matters, but then again you are already using `ORDER BY`, so I didn't bother to comment on that. — Tim Biegeleisen, Oct 29 '19 at 15:19

Vo_Vik · Answer 1 · 2019-10-29T16:04:34.540

After more research I will answer my own question.

I guess in most situations this will be true, but I was able to find cases when this algorithm could cause problem. First is mentioned in my UPDATE to the question regarding clusters solutions. Second, I can imagine is situation when table has gaps in id - auto_increment field (initially started from 1000000 not 0) and during execution of insert statement auto_increment value manually changed to lower value. So this will break auto_increment pattern. I would suggest instead use order by some meaningful fields that we can predict uniqueness. If there are non, then there are not difference which from 2 identical record that was just inserted to use.

Regarding question from title. Auto_increment values in single bulk insert in single MySQL instance usually will be in growing order, but there are cases when it could be interrupted with auto_increment value changed to lower. On cluster solution it depend on cluster implementation, and most likely will be not predictable too.

What is auto_increment fields values order for MySQL INSERT .. SELECT statement

1 Answers1