Easily master last_insert_id() in MySQL function

Mysql relational database management system

MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.

I believe everyone should know that the Mysql function can achieve many functions we need. The Mysql function Last_insert_id() introduced in this article is one of them. The article expands through an example. Speaking of it, it should be more helpful for everyone's understanding and learning. Friends in need can come and take a look below.

Preface

Recently a colleague asked me why the results obtained by last_insert_id() were different from expected, so I studied this parameter seriously. The following is For a detailed introduction to last_insert_id(), let’s learn together.

First of all, give an example

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: 0
wing@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: 0
wing@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)

Secondly, study it

It is really important to check the MySQL official documentation. . .

Official source: http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-id

Official document original Words:

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.

Translation:

last_insert_id() without parameters returns the first automatically generated value of the most recent INSERT statement executed against the autoincrement column.

Original words of the official document:

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the 
first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Translation:

If you insert multiple values ​​​​in a single INSERT statement, then last_insert_id( ) returns the first automatically generated value of the INSERT statement.

Then, analyze it

Please read the black font in the above translation carefully and keep in mind the constraints of last_insert_id().

Why does last_insert_id() fail when the specified value is inserted?

The official document clearly states that it is an automatically generated value, not a value you specify. It is generated by the autoincremnt counter itself and can be tracked by last_insert_id(). .

Why is the first inserted value displayed when inserting multiple values? Doesn’t last mean the last value? .

The official document clearly states that it is the first value automatically generated by the most recent INSERT statement** Wow wow wow. .

Summary

Remember the constraints of last_insert_id(). The first value automatically generated on the autpincrement column by the most recent INSERT statement. The summarized sentence feels much smoother than the translated sentence==

Okay, the above is the entire content of this article. For more related content, please pay attention to the PHP Chinese website (www.php. cn)!