Behavior of LAST_INSERT_ID

This topic describes the behavior of the LAST_INSERT_ID() function in OceanBase Database and the differences between OceanBase Database and MySQL.

The LAST_INSERT_ID() function is used to obtain the auto-increment ID value generated by the most recent INSERT operation. In OceanBase Database, the behavior of this function differs from that in MySQL in the following two aspects:

• Session-level LAST_INSERT_ID: The value is read and modified by using the LAST_INSERT_ID() function.
• Protocol-level LAST_INSERT_ID: The value is returned in the OK packet of the MySQL protocol.

MySQL behavior

Session-level LAST_INSERT_ID

The LAST_INSERT_ID value of a session can be read and modified by using the LAST_INSERT_ID(args) function:

-- Read the LAST_INSERT_ID value of a session.
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+

-- Modify the LAST_INSERT_ID value of a session by using the function.
SELECT LAST_INSERT_ID(10);
+--------------------+
| LAST_INSERT_ID(10) |
+--------------------+
|                 10 |
+--------------------+

Protocol-level LAST_INSERT_ID

The LAST_INSERT_ID value in the OK packet of the MySQL protocol. When a DML statement modifies the LAST_INSERT_ID value of the session, the LAST_INSERT_ID value in the OK packet returned to the client after the DML statement is executed will be equivalent to the LAST_INSERT_ID value of the session. Otherwise, the LAST_INSERT_ID value returned to the client will be the value of the AUTO_INCREMENT column of the last row written by the DML operator.

Compatibility with MySQL databases

Fully compatible behaviors

OceanBase Database is fully compatible with MySQL in the following aspects:

• Reading and modifying session-level LAST_INSERT_ID: The value of LAST_INSERT_ID can be read and modified through the LAST_INSERT_ID() function.
• Setting the LAST_INSERT_ID value through a function expression: The value can be set using the LAST_INSERT_ID(expr) function.
• OK packet format at the protocol level: The response packet format for clients is consistent with MySQL.
• REPLACE statements: The value of the auto-increment column is returned for the first INSERT or the value of the auto-increment column that was overwritten.
• INSERT ... ON DUPLICATE KEY UPDATE statements: If there are no conflicts, the value of the auto-increment column of the first INSERT statement is returned. If there are conflicts, the value of the auto-increment column of the first INSERT statement is returned. If all the rows are in conflict, the value remains unchanged.
• Multi-row inserts: The value of the auto-increment column of the first INSERT statement is returned.
• IGNORE statements: If the primary key conflict causes the data to be not written, LAST_INSERT_ID will not be modified.

Differences in behavior

The main differences between OceanBase Database and MySQL are as follows:

Specifying an auto-increment column in an INSERT statement

MySQL behavior:

• When an auto-increment column is manually specified: The value of LAST_INSERT_ID remains unchanged.

OceanBase Database behavior:

• When an auto-increment column is manually specified: The value of the auto-increment column returned is the ID of the first inserted row.

-- Create a test table.
CREATE TABLE `t1` (
  `c1` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `c2` INT DEFAULT NULL,
  PRIMARY KEY (`c1`),
  UNIQUE KEY `c2` (`c2`)
);

-- Example 1: Modify the value using the LAST_INSERT_ID function (behavior is consistent between MySQL and OceanBase)
UPDATE t2 SET c1 = LAST_INSERT_ID(100) + c1 WHERE c2 = 1;
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

-- Example 2: INSERT statement (behavior is consistent between MySQL and OceanBase)
INSERT INTO t1(c2) VALUES (1);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

-- Example 3: REPLACE statement (behavior is consistent between MySQL and OceanBase)
REPLACE INTO t1(c2) VALUES (2);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

-- Example 4: Multi-row insertion (both MySQL and OceanBase return the ID of the first inserted row)
INSERT INTO t1(c2) VALUES (3), (4);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+

Detailed comparison table

Compatibility plan