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

actual result

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.

Answer 1

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