Behavior of LAST_INSERT_ID

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

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

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

MySQL behavior

Session-level LAST_INSERT_ID

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

-- Read the LAST_INSERT_ID for the current session.
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+

-- Modify the LAST_INSERT_ID for the current session.
SELECT LAST_INSERT_ID(10);
+--------------------+
| LAST_INSERT_ID(10) |
+--------------------+
|                 10 |
+--------------------+

Protocol-level LAST_INSERT_ID

In the OK packet of the MySQL protocol, the LAST_INSERT_ID field is used. If a DML statement modifies the LAST_INSERT_ID for the session, the LAST_INSERT_ID in the OK packet returned to the client after the DML statement execution will be equivalent to the session's LAST_INSERT_ID. Otherwise, the LAST_INSERT_ID returned to the client will be the value of the AUTO_INCREMENT column for the last row written by the DML operator.

Compatibility with MySQL

Fully compatible behaviors

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

• Session-level LAST_INSERT_ID read and modification: Use the LAST_INSERT_ID() function to read and modify the value on the session.
• Setting LAST_INSERT_ID values using function expressions: Use the LAST_INSERT_ID(expr) function to set the value.
• OK packet format at the protocol level: Maintain consistency with MySQL in the response packet format for clients.
• REPLACE statements: Return the value of the auto-increment column from the first INSERT or overwrite write.
• INSERT ... ON DUPLICATE KEY UPDATE: Return the value of the auto-increment column from the first INSERT when there are no conflicts, return the value of the auto-increment column from the first INSERT when there are partial conflicts, and maintain the value when there are full conflicts.
• Multi-row inserts: Return the value of the auto-increment column from the first INSERT.
• IGNORE statements: If data is not written due to a primary key conflict, LAST_INSERT_ID will not be modified.

Behavior differences

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

Manually specifying the auto-increment column in an INSERT statement

MySQL behavior:

• Manually specify the auto-increment column: Maintain LAST_INSERT_ID unchanged.

OceanBase Database behavior:

• Manually specify the auto-increment column: Return the value of the auto-increment column from the first INSERT.

-- 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 Database).
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 Database).
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 Database).
REPLACE INTO t1(c2) VALUES (2);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

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

Comparison table

Compatibility plan