This topic describes the behavior of the LAST_INSERT_ID() function in OceanBase AI Database and its differences from MySQL.
The LAST_INSERT_ID() function is used to obtain the auto-increment ID generated by the most recent INSERT operation. In OceanBase AI Database, its behavior differs from MySQL in the following two aspects:
- Session-level LAST_INSERT_ID: Can be read and modified using the
LAST_INSERT_ID()function. - Protocol-level LAST_INSERT_ID: The value returned in the MySQL protocol OK packet.
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 LAST_INSERT_ID on the session
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0 |
+------------------+
-- Modify the LAST_INSERT_ID on a session by using a 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 for the session, the LAST_INSERT_ID in the OK packet returned to the client after the DML 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 statement.
Compatibility between OceanBase AI Database and MySQL
Fully compatible behavior
OceanBase AI Database is fully compatible with MySQL in the following aspects:
- Reading and modifying the session-level
LAST_INSERT_ID: Use theLAST_INSERT_ID()function to read from and modify the value for the current session. - Set the
LAST_INSERT_IDvalue using a function expression: Use theLAST_INSERT_ID(expr)function to set the value. - Protocol-level OK packet format: The response packet format to the client is consistent with that of MySQL.
- REPLACE statement: Returns the value of the auto-increment column for the first row inserted or overwritten.
- INSERT ... ON DUPLICATE KEY UPDATE: If no conflicts occur, returns the value of the auto-increment column for the first row of the INSERT statement. If partial conflicts occur, returns the value of the auto-increment column for the first row of the INSERT statement. If all rows are involved in a conflict, the value remains unchanged.
- Multi-row insertion: Returns the value of the auto-increment column for the first row inserted.
- IGNORE statement: When data is not written due to a primary key conflict,
LAST_INSERT_IDis not modified.
Note
OceanBase AI Database, like MySQL, stores the LAST_INSERT_ID in a session. The behavior of obtaining and modifying the LAST_INSERT_ID in a session using expressions is identical in OceanBase AI Database and MySQL.
Behavioral differences
The main differences between OceanBase AI Database and MySQL are as follows:
Notice
OceanBase currently maintains consistency with MySQL in the packet format returned to clients. That is, the OK packet contains LAST_INSERT_ID information. However, the behavior of this information is not fully compatible with MySQL. When writing to an AUTO_INCREMENT column using DML statements, the change in the value of LAST_INSERT_ID is not fully compatible with MySQL.
Manually specify auto-increment columns in the INSERT statement
MySQL behavior:
- Manually specify the auto-increment column: Keep
LAST_INSERT_IDunchanged.
Behavior of OceanBase AI Database:
- Manually specify the auto-increment column: Return the value of the auto-increment column for the first row inserted.
Note
The following table describes the differences between OceanBase AI Database and MySQL in the behavior of LAST_INSERT_ID. In OceanBase AI Database, it is impossible to distinguish whether the current value of an auto-increment column is manually specified or obtained by the auto-increment service. Therefore, the behavior of LAST_INSERT_ID is consistent in both scenarios.
-- 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 a value using the LAST_INSERT_ID function (MySQL and OceanBase behave consistently).
UPDATE t2 SET c1 = LAST_INSERT_ID(100) + c1 WHERE c2 = 1;
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 100 |
+------------------+
-- Example 2: INSERT statement (MySQL and OceanBase behave the same)
INSERT INTO t1(c2) VALUES (1);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
-- Example 3: REPLACE statement (MySQL and OceanBase behave the same)
REPLACE INTO t1(c2) VALUES (2);
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
-- Example 4: Multiple-row insertion (both MySQL and OceanBase 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
DML Statement Types |
Scenario |
LAST_INSERT_ID on MySQL Sessions |
LAST_INSERT_ID on OceanBase Sessions |
Differences |
|---|---|---|---|---|
| INSERT | Primary key conflict | unchanged | unchanged | Consistent |
| Primary key does not conflict (auto-increment column not specified) | The value of the auto-increment column in the first INSERT statement | The value of the auto-increment column in the first INSERT statement | Consistent | |
| Primary key does not conflict (auto-increment column specified manually) | unchanged | The value of the auto-increment column in the first INSERT statement | OceanBase will update the value | |
| REPLACE | The value of the auto-increment column in the first row of the INSERT statement or the value written over it | The value of the auto-increment column in the first row of the INSERT statement or the value written over it | Consistent | |
| INSERT ... ON DUPLICATE KEY | None Conflicts | The value of the auto-increment column in the first INSERT statement | The value of the auto-increment column in the first INSERT statement | Consistent |
| Partial conflict | The value of the auto-increment column in the first INSERT statement | The value of the auto-increment column in the first INSERT statement | Consistent | |
| All Conflicts | unchanged | unchanged | Consistent |