Mysql LAST_INSERT_ID函数有两种表达形式,分别是:
LAST_INSERT_ID()
LAST_INSERT_ID(expr)
LAST_INSERT_ID()
With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.
无参LAST_INSERT_ID()函数,会返回最近一条成功INSERT语句中AUTO_INCREMENT列的第一个值。
需要注意的是:
-
LAST_INSERT_ID返回值和table是无关的,返回最近一条成功INSERT语句中AUTO_INCREMENT列的值;向tableA插入数据后,再向tableB插入数据,LAST_INSERT_ID返回tableB中的AUTO_INCREMENT列的值;
-
如果INSERT语句插入多个行, LAST_INSERT_ID() 只返回插入的第一行数据时产生的值;
CREATE TABLE t (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL
);
INSERT INTO t VALUES (NULL, 'Bob');
SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1|
+------------------+
INSERT INTO t VALUES
(NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2|
+------------------+
-
如果使用INSERT IGNORE,并且对应INSERT语句被IGNORE, LAST_INSERT_ID() 的值将不会改变。
-
LAST_INSERT_ID()返回的这个ID值是以Connection维度存储在数据库服务端的,这意味着它返回的值是本次Connection上一个插入语句所产生的第一个自增值,就算其他Connection也对自增值进行了操作,当前Connection的这个值也不会受到影响。这个特性保证了每个Connection可以取回自己当前的ID值,不存在并发问题。
-
在Mysql里,SELECT LAST_INSERT_ID() 如果后面带上表名,会返回表记录数个结果。在Mybatis,如果配合SelectKey使用,会导致
SelectKey returned more than one value.
异常
SELECT LAST_INSERT_ID() FROM table;
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 0|
+------------------+
| 0|
+------------------+
| 0|
+------------------+
LAST_INSERT_ID(expr)
With an argument, LAST_INSERT_ID() returns an unsigned integer.
返回一个无符号整型,具体值是:expr计算转换后的结果。
select last_insert_id(1);
1
select last_insert_id(1+1);
2
select last_insert_id(1*2);
2
select last_insert_id(0.3);
0
select last_insert_id(0.5);
1