The LAST_INSERT_ID function returns the value created at the end of the AUTO_INCREMENT column of all tables.
If no values are INSERTed successfully, the last successful value will be maintained, and the SQL statement on execution does not affect the LAST_INSERT_ID() value. If you enter multiple rows with one INSERT statement, the LAST_INSERT_ID() will return the input row value entered at the end. If the execution result of the previous SQL statement returns an error, the LAST_INSERT_ID() value is not defined, and the rollback can not recover the LAST_INSERT_ID() value as the previous transaction value.
You can not check the LAST_INSERT_ID() value used in the trigger, outside trigger.
The created ID is maintained independently for the connection of each client.
LAST_INSERT_ID()
CREATE TABLE ss (id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, text VARCHAR(32));
INSERT into ss VALUES(NULL,’cubrid’);
SELECT LAST_INSERT_ID();
last_insert_id()
=======================
1
INSERT INTO ss VALUES(NULL,’database’),(NULL,’manager’);
SELECT LAST_INSERT_ID();
last_insert_id()
=======================
3
If you insert multiple rows with a single INSERT statement, LAST_INSERT_ID() returns the first AUTO_INCREMENT value.
CREATE TABLE tbl (id INT AUTO_INCREMENT);
INSERT INTO tbl values (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();
last_insert_id()
=======================
1
INSERT INTO tbl values (500), (NULL), (NULL);
SELECT LAST_INSERT_ID();
last_insert_id()
=======================
3
SELECT * FROM tbl;
id
=======================
500
1
2
500
3
4