Behavior of LAST_INSERT_ID

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

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

• 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

You can read and modify the LAST_INSERT_ID value of a session 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. If a DML statement modifies the LAST_INSERT_ID value of a session, the LAST_INSERT_ID value in the OK packet returned to the client after the DML statement is executed is equivalent to the LAST_INSERT_ID value of the session. Otherwise, the LAST_INSERT_ID value returned to the client is the value of the AUTO_INCREMENT column of the last row written by the DML operator.

Compatibility between seekdb and MySQL

Fully compatible behaviors

seekdb is fully compatible with MySQL in the following aspects:

• Reading and modifying the LAST_INSERT_ID value of a session: You can read and modify the LAST_INSERT_ID value of a session by using the LAST_INSERT_ID() function.
• Setting the LAST_INSERT_ID value by using a function expression: You can set the LAST_INSERT_ID value by using the LAST_INSERT_ID(expr) function.
• OK packet format: The format of the OK packet returned to the client is consistent with that of MySQL.
• REPLACE statements: The value of the AUTO_INCREMENT column of the first row inserted or overwritten is returned.
• INSERT ... ON DUPLICATE KEY UPDATE statements: If no rows are affected, the value of the AUTO_INCREMENT column of the first row inserted is returned. If some rows are affected, the value of the AUTO_INCREMENT column of the first row inserted is returned. If all rows are affected, the value remains unchanged.
• Multi-row inserts: The value of the AUTO_INCREMENT column of the first row inserted is returned.
• IGNORE statements: If the primary key conflict causes the data to be not written, the LAST_INSERT_ID value is not modified.

info

seekdb also stores the LAST_INSERT_ID value of a session, and you can obtain and modify the LAST_INSERT_ID value of a session by using expressions. The behavior of seekdb is consistent with that of MySQL.

Differences in behavior

The main differences between seekdb and MySQL are as follows:

tip

seekdb keeps the OK packet format consistent with that of MySQL, that is, the OK packet contains the LAST_INSERT_ID information. However, the LAST_INSERT_ID information is not fully consistent with that of MySQL. The value of the LAST_INSERT_ID field changes in a way that is not fully compatible with that of MySQL when you write data to the AUTO_INCREMENT column by using DML statements.

Specifying the AUTO_INCREMENT column in INSERT statements

MySQL behavior:

• If you manually specify the AUTO_INCREMENT column, the LAST_INSERT_ID value remains unchanged.

seekdb behavior:

• If you manually specify the AUTO_INCREMENT column, the value of the AUTO_INCREMENT column of the first row inserted is returned.

tip

This is the main difference between seekdb and MySQL in the behavior of LAST_INSERT_ID. seekdb cannot distinguish whether the current value of the AUTO_INCREMENT column is manually specified or obtained from the auto-increment service. Therefore, the behavior of LAST_INSERT_ID is consistent.

-- 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 seekdb is consistent.)
UPDATE t2 SET c1 = LAST_INSERT_ID(100) + c1 WHERE c2 = 1;
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              100 |
+------------------+

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

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

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

Comparison table

DML statement type	Scenario	MySQL session's LAST_INSERT_ID	seekdb session's LAST_INSERT_ID	Description
INSERT	Primary key conflict	The value remains unchanged.	The value remains unchanged.	Consistent
	No primary key conflict and the AUTO_INCREMENT column is not specified	The value of the AUTO_INCREMENT column of the first row inserted.	The value of the AUTO_INCREMENT column of the first row inserted.	Consistent
	No primary key conflict and the AUTO_INCREMENT column is specified manually	The value remains unchanged.	The value of the AUTO_INCREMENT column of the first row inserted.	seekdb updates the value.
REPLACE		The value of the AUTO_INCREMENT column of the first row inserted or overwritten.	The value of the AUTO_INCREMENT column of the first row inserted or overwritten.	Consistent
INSERT ... ON DUPLICATE KEY	No rows are affected	The value of the AUTO_INCREMENT column of the first row inserted.	The value of the AUTO_INCREMENT column of the first row inserted.	Consistent
	Some rows are affected	The value of the AUTO_INCREMENT column of the first row inserted.	The value of the AUTO_INCREMENT column of the first row inserted.	Consistent
	All rows are affected	The value remains unchanged.	The value remains unchanged.	Consistent