insert many rows, LAST_INSERT_ID return 1

Question

Have:

1. create DB
2. create Table
3. insert 3 rows
4. select LAST_INSERT_ID()

Here test code:

DROP DATABASE IF EXISTS TEST;
CREATE DATABASE TEST;
USE TEST;
CREATE TABLE test (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        age INT
    );

INSERT INTO test (age) VALUES (1), (2), (3);

SELECT LAST_INSERT_ID();

Why LAST_INSERT_ID() return 1 ? Excepted: 3

How to get valid LAST_INSERT_ID() ?

mysql Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu) – Vitaly Fadeev Aug 23 '19 at 05:26 — Vitaly Fadeev, Aug 23 '19 at 05:26

score 2 · Accepted Answer · answered Aug 23 '19 at 05:28

The MySQL documentation clearly explains this behavior:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first (emphasis mine) automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

The first value generated by the auto increment sequence in the insert was 1, not 2 or 3, so the value 1 gets returned.

I think the confusion you have is around the name LAST_INSERT_ID. The "last" part refers to the most recent insert statement, not the most recent id value within that insert.

insert many rows, LAST_INSERT_ID return 1

1 Answers1