Behavior of LAST_INSERT_ID

This topic describes the behavior of the LAST_INSERT_ID() function in OceanBase Database and the differences between it and the corresponding function in 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. The main differences are as follows:

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

MySQL behavior

Session-level LAST_INSERT_ID

The LAST_INSERT_ID() function is used to read and modify the LAST_INSERT_ID value for the current session:

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

-- Modify the LAST_INSERT_ID value for the current session 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 for the current 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 for the current session. Otherwise, the LAST_INSERT_ID value returned to the client will be the value of the AUTO_INCREMENT column for the last row inserted 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 reads and modifications: The LAST_INSERT_ID() function is used to read and modify the session value.
• Setting the LAST_INSERT_ID value via a function expression: The LAST_INSERT_ID(expr) function is used to set the value.
• OK packet format at the protocol level: The response format to clients is consistent with MySQL.
• REPLACE statements: Returns the value of the first row's auto-increment column, whether it's an INSERT or an overwrite.
• INSERT ... ON DUPLICATE KEY UPDATE: Returns the value of the first row's auto-increment column in all cases, whether there are conflicts or not.
• Multi-row inserts: Returns the value of the first row's auto-increment column.
• IGNORE statements: If a primary key conflict prevents data from being written, LAST_INSERT_ID is not 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:

• If you manually specify the auto-increment column, the LAST_INSERT_ID value remains unchanged.

OceanBase Database behavior:

• If you manually specify the auto-increment column, the value of the auto-increment column in the first row of the INSERT statement is returned.

-- 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 |
+------------------+

Detailed comparison table

Compatibility planning