Easy understanding of last_insert_id () and mysqllastinsertid in MySQL Functions

Easy understanding of last_insert_id () and mysqllastinsertid in MySQL Functions

Preface

A recent colleague asked me whylast_insert_id()The result is different from the expected one. So I will study this parameter carefully. The following is aboutlast_insert_id()For more information, see.

First, let's give an example.

Wing @ 3306> show create table tt; + ------- + keys + | Table | Create Table | + ------- + keys + | tt | create table 'TT' ('id' int (11) not null AUTO_INCREMENT, primary key ('id') ENG INE = InnoDB default charset = utf8 | + ------- + rows + 1 row in set (0.00 sec) # When no value is specified, last_insert_id () expected 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? Shouldn't it be 5? Why is it the first inserted value 3? Last_insert_id does not match the expectation .. 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) # Nana? As expected, isn't it 10? Why is it the last 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)

Next, study

Reading the MySQL official documentation is really too important...

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

Original official document:

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:

No Parameterlast_insert_id()The returned result is the last execution on the autoincrement column.INSERTThe first automatically generated value of the statement.

Original 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 are in a singleINSERTInsert multiple values in the statement.last_insert_id()The returned result isINSERTThe first automatically generated value of the statement.

Then, analyze

Please carefully read the black font in the above translation, rememberlast_insert_id().

Why Insert the specified value,last_insert_id()Is it invalid?

The official document clearly states that the value is automatically generated, not the value you specified. It can only be generated by the autoincremnt counter.last_insert_id()Wow ..

Why is the first inserted value displayed during multi-value insertion,lastIsn't that the last value ..

The official document clearly states that it was the last timeINSERTThe first value automatically generated by the statement ** wow ..

Summary

Rememberlast_insert_id(). Last timeINSERTThe first value automatically generated by the statement in the autpincrement column. This sentence is more convenient than the sentence translated =

Well, the above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.