LAST_INSERT_ID Function

Description

The LAST_INSERT_ID function returns the value that has been most recently inserted to the AUTO_INCREMENT column by a single INSERT statement. The value returned by the LAST_INSERT_ID function has the following characteristics.

• The LAST_INSERT_ID() will return the first entered AUTO_INCREMENT() value in the INSERT statement with multiple rows (ex. INSERT INTO tbl VALUES (), (), …, ()).
• The LAST_INSERT_ID() value will not be back to the state in the transaction began even though rollback is performed.
• The LAST_INSERT_ID() value used within the trigger cannot be verified outside trigger.
• Each LAST_INSERT_ID is working independently for applications.

Syntax

LAST_INSERT_ID()

Example 1

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

Example 2

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