A2-02-26.DML- MySQL LAST_INSERT_ID Function
转载自：http://www.mysqltutorial.org/mysql-last_insert_id.aspx
MySQL LAST_INSERT_ID Function
Summary: in this tutorial, you will learn how to use the MySQL LAST_INSERT_ID function to obtain the generated sequence number of the last insert row.
Introduction to MySQL LAST_INSERT_ID function
In database design, you often use a surrogate key to generate unique integer values for the primary key column using the AUTO_INCREMENT attribute.
It means when you insert a new row into the table that has an AUTO_INCREMENT column, MySQL automatically generates a unique integer and use it for the column.
For more information on how to set the AUTO_INCREMENT attribute for a column, check it out the MySQL sequence tutorial.
You can obtain the generated sequence number using the MySQL LAST_INSERT_ID function and use the number for the next statements e.g., inserting a new row into a correlated table.
MySQL LAST_INSERT_ID example
Let’s take a look at an example of using MySQL LAST_INSERT_ID function.
First, create a new table named tbl for testing. In the tbl table, you set the AUTO_INCREMENT attribute for the id column.
|
1
2
3
4
|
CREATE TABLE tbl (
id INT AUTO_INCREMENT PRIMARY KEY,
description VARCHAR(250) NOT NULL
);
|
Second, insert a new row into the tbl table.
|
1
2
|
INSERT INTO tbl(description)
VALUES('MySQL last_insert_id');
|
Third, use the MySQL LAST_INSERT_ID function to get the last insert id that MySQL has been generated.
|
1
|
SELECT LAST_INSERT_ID();
|
It’s important to note that if you insert multiple rows into a table using a single INSERT statement, the LAST_INSERT_ID function returns the last insert id of the first row.
Suppose the AUTO_INCREMENT column has current value as 1 and you insert 3 rows into the table. When you use the LAST_INSERT_ID function to get the last insert id, you will get 2 instead of 4.
The following statement inserts 3 rows into the tbl table and gets the last insert id using the LAST_INSERT_ID function.
|
1
2
3
4
5
6
|
INSERT INTO tbl(description)
VALUES('record 1'),
('record 2'),
('record 3');
SELECT LAST_INSERT_ID();
|
Check the data of the tbl table:
|
1
|
SELECT * FROM tbl;
|
The LAST_INSERT_ID function works based on client-independent principle. It means the value returned by the LAST_INSERT_ID function for a specific client is the value generated by that client only. This ensures that each client can obtain its own unique ID.
In this tutorial, we have shown you how to use the MySQL LAST_INSERT_ID function to get the sequence number of the last row that has been inserted into a table.
