Auto increment id field is not add 1 after executing MySQL bulk insert query

Question

While testing the user table I made, I found something strange. When adding a single row after executing a query that bulk inserts multiple users as shown below, the value of the id field increases by 2 instead of 1.

bulk insert query

INSERT INTO USER_TB (ID,NICKNAME,USER_LV,EXP) VALUES (1,'test1',1,0)
,(NULL,'test2',1,0)
,(NULL,'test3',1,0)
,(NULL,'test4',1,0);

single row insert query

INSERT INTO USER_TB (ID,NICKNAME,USER_LV,EXP) VALUES (NULL,'test5',1,0);

Expected Result

I expected the look of the following table.

actual result

But I had to look at the following table.

It works fine if you don't force the id field in bulk insert query. But I want to know why this is happening.

Thanks in advance.

That is not reproducable see https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=99fafb72558560d2656d5751f66a084e as long as you don't delete rows, it should give you always the nex number, hwat could happne, ist that the "order" could get messup — nbk, Jul 17 '20 at 14:43
I can not reproduce your problme, see fiddle, so in my experience skips doesn't happen, without user action like **removing** a row. of course when you have an heavy duty server with lots and lots of traffic and different entitys mysql or processor, can rearrange code, i could think of a psoobilty that a number has skiped, but a bulk insert isn't a reason. that said, as i mentioned that there are entitys that can rearrange code, it can happen that test1 is after test 3 even with a sequential number. — nbk, Jul 17 '20 at 15:18
I reproduced the problem in your fiddle. See https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=66fb8d1aff6b47d2394ece5e4256807d — jinyoung yoo, Jul 17 '20 at 15:25
Related: https://stackoverflow.com/questions/17798835/auto-increment-skipping-numbers — Barmar, Jul 17 '20 at 15:54

nbk · Accepted Answer · 2020-07-17T15:46:34.537

That is actually quite tricky

CREATE TABLE USER_TB (ID INT Auto_inCREMENT Primary KEY,
NICKNAME varchar(50)
,USER_LV INT ,
EXP BIGINT)

INSERT INTO USER_TB (ID,NICKNAME,USER_LV,EXP) VALUES (NULL,'test1',1,0),(NULL,'test2',1,0),(NULL,'test3',1,0),(NULL,'test4',1,0),
(NULL,'test5',1,0),(NULL,'test6',1,0),(NULL,'test7',1,0),(NULL,'test8',1,0),
(NULL,'test9',1,0),(NULL,'test10',1,0),(NULL,'test11',1,0),(NULL,'test12',1,0);

SELECT LAST_INSERT_ID();

| LAST_INSERT_ID() |
| ---------------: |
|                1 |

INSERT INTO USER_TB VALUES (NULL,'test13',1,0),(NULL,'test14',1,0)

SELECT LAST_INSERT_ID();

| LAST_INSERT_ID() |
| ---------------: |
|               13 |

SELECT * FROM USER_TB;

ID | NICKNAME | USER_LV | EXP
-: | :------- | ------: | --:
 1 | test1    |       1 |   0
 2 | test2    |       1 |   0
 3 | test3    |       1 |   0
 4 | test4    |       1 |   0
 5 | test5    |       1 |   0
 6 | test6    |       1 |   0
 7 | test7    |       1 |   0
 8 | test8    |       1 |   0
 9 | test9    |       1 |   0
10 | test10   |       1 |   0
11 | test11   |       1 |   0
12 | test12   |       1 |   0
13 | test13   |       1 |   0
14 | test14   |       1 |   0

db<>fiddle here

What your insert has differnet is the first

(1,'test1',1,0)

and so mysql counts +1 so it seems like it skipped 1 number

If i put as first insert

(2,'test1',1,0)

It starts at 2 and "skips" also 2 at the end of the first INSERT, because in his internal counter he has 14 (12 + 2)

CREATE TABLE USER_TB (ID INT Auto_inCREMENT Primary KEY,
NICKNAME varchar(50)
,USER_LV INT ,
EXP BIGINT)

INSERT INTO USER_TB (ID,NICKNAME,USER_LV,EXP) VALUES (2,'test1',1,0),(NULL,'test2',1,0),(NULL,'test3',1,0),(NULL,'test4',1,0),
(NULL,'test5',1,0),(NULL,'test6',1,0),(NULL,'test7',1,0),(NULL,'test8',1,0),
(NULL,'test9',1,0),(NULL,'test10',1,0),(NULL,'test11',1,0),(NULL,'test12',1,0);

SELECT LAST_INSERT_ID();

| LAST_INSERT_ID() |
| ---------------: |
|                3 |

INSERT INTO USER_TB VALUES (NULL,'test13',1,0),(NULL,'test14',1,0)

SELECT LAST_INSERT_ID();

| LAST_INSERT_ID() |
| ---------------: |
|               15 |

SELECT * FROM USER_TB;

ID | NICKNAME | USER_LV | EXP
-: | :------- | ------: | --:
 2 | test1    |       1 |   0
 3 | test2    |       1 |   0
 4 | test3    |       1 |   0
 5 | test4    |       1 |   0
 6 | test5    |       1 |   0
 7 | test6    |       1 |   0
 8 | test7    |       1 |   0
 9 | test8    |       1 |   0
10 | test9    |       1 |   0
11 | test10   |       1 |   0
12 | test11   |       1 |   0
13 | test12   |       1 |   0
15 | test13   |       1 |   0
16 | test14   |       1 |   0

db<>fiddle here

so start with (NULL,'test1',1,0) and everything works as expected

Is the auto increment value determined by the internal counter value? and What is the relationship between Internal counter and LAST_INSERT_ID? — jinyoung yoo, Jul 17 '20 at 16:18

Auto increment id field is not add 1 after executing MySQL bulk insert query

1 Answers1