Behavior of LAST_INSERT_ID

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

The LAST_INSERT_ID() function is used to obtain the auto-increment ID value generated by the last INSERT operation. In OceanBase Database, this function behaves differently from the corresponding function in MySQL. The differences are as follows:

• Session-level LAST_INSERT_ID: This value can be 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 of a session can be read and modified using the LAST_INSERT_ID(args) function:

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

-- Modify the LAST_INSERT_ID of the session using the function
SELECT LAST_INSERT_ID(10);
+--------------------+
| LAST_INSERT_ID(10) |
+--------------------+
|                 10 |
+--------------------+

Protocol-level LAST_INSERT_ID

The LAST_INSERT_ID in the OK packet of the MySQL protocol. When a DML statement modifies the LAST_INSERT_ID of a session, the LAST_INSERT_ID in the OK packet returned to the client after the DML statement is executed is equivalent to the LAST_INSERT_ID of the session. Otherwise, the LAST_INSERT_ID returned to the client is the value of the AUTO_INCREMENT column of 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: Read and modify the value on the session by using the LAST_INSERT_ID() function.
• Setting LAST_INSERT_ID values by using function expressions: Set the value by using the LAST_INSERT_ID(expr) function.
• OK packet format at the protocol level: Maintain the same OK packet format as MySQL for responses to clients.
• REPLACE statements: Return the value of the auto-increment column of the first row of the INSERT or overwrite operation.
• INSERT ... ON DUPLICATE KEY UPDATE: Return the value of the auto-increment column of the first row of the INSERT when no conflicts occur, return the value of the auto-increment column of the first row of the INSERT when some conflicts occur, and maintain the value when all conflicts occur.
• Multi-row inserts: Return the value of the auto-increment column of the first row of the INSERT.
• IGNORE statements: If the data is not written due to a primary key conflict, the LAST_INSERT_ID value is not modified.

Differences in behavior

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

Manually specifying the auto-increment column in INSERT statements

MySQL behavior:

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

OceanBase Database behavior:

• Manually specify the auto-increment column: Return the value of the auto-increment column of the first row of the 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 by using the LAST_INSERT_ID function (the behavior of MySQL and OceanBase Database is consistent).
UPDATE t2 SET c1 = LAST_INSERT_ID(100) + c1 WHERE c2 = 1;
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

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

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

-- Example 4: Perform multi-row inserts (MySQL and OceanBase Database both 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 planning