轻松掌握MySQL函数中的last_insert_id()

2024-07-24 12:53:05

字体：大中小

来源：转载

供稿：网友

前言

最近一个同事问我，为什么last_insert_id()得到的结果与预期的不一样呢，于是我就认真的去研究的一下这个参数，下面是关于last_insert_id()的详细介绍，一起来学习学习吧。

首先，举个例子

wing@3306>show create table tt;+-------+-----------------------------------------------------------------------------------------------------------------------+| Table | Create Table |+-------+-----------------------------------------------------------------------------------------------------------------------+| tt | CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)# 没有指定值的时候，last_insert_id()符合预期希望wing@3306>insert into tt values();Query OK, 1 row affected (0.00 sec)wing@3306>select last_insert_id();+------------------+| last_insert_id() |+------------------+| 1 |+------------------+1 row in set (0.00 sec)wing@3306>insert into tt values();Query OK, 1 row affected (0.00 sec)wing@3306>select last_insert_id();+------------------+| last_insert_id() |+------------------+| 2 |+------------------+1 row in set (0.00 sec)# what?不是应该是5么，为什么是第一个插入的值3？last_insert_id开始有一点不符合预期了。。wing@3306>insert into tt values(),(),();Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0wing@3306>select last_insert_id();+------------------+| last_insert_id() |+------------------+| 3 |+------------------+1 row in set (0.00 sec)wing@3306>insert into tt values(),(),();Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0wing@3306>select last_insert_id();+------------------+| last_insert_id() |+------------------+| 6 |+------------------+1 row in set (0.00 sec)# 纳尼？按照预期不是10么？为什么还是之前的6？last_insert_id()我不懂你啊。。wing@3306>insert into tt values(10);Query OK, 1 row affected (0.01 sec)wing@3306>select last_insert_id();+------------------+| last_insert_id() |+------------------+| 6 |+------------------+1 row in set (0.00 sec)

其次，研究一下

查阅MySQL官方文档，真的太重要了。。。

官方出处：http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

官方文档原话：

With no argument, LAST_INSERT_ID() returns a 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.

翻译：

没有参数的last_insert_id()返回的是最近一次针对autoincrement列执行的

上一篇：浅谈MySQL模糊查询中通配符的转义

下一篇：详解如何使用DockerHub官方的MySQL镜像