Easily master the last_insert_id () _mysql in Mysql functions

Objective

Recently, a colleague asked me why the last_insert_id() results are not the same as expected, so I seriously to study this parameter, the following is about last_insert_id() the detailed introduction, together to learn.

First of all, let's give an example

Wing@3306>show CREATE TABLE TT; +-------+------------------------------------------------------------------------------------------------------ -----------------+
| Table |
Create Table | +-------+------------------------------------------------------------------------------------------------------ -----------------+
| tt |
CREATE TABLE ' TT ' (' id ' int (one) not NULL auto_increment, PRIMARY KEY (' id ')) engine=innodb DEFAULT Charset=utf8 | +-------+------------------------------------------------------------------------------------------------------
-----------------+ 1 row in Set (0.00 sec) # When no value is specified, last_insert_id () conforms to expectations 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? is not supposed to be 5, why is the first inserted value 3?
LAST_INSERT_ID started a bit out of line with expectations.
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) # Nani? Not 10, according to expectations? Why is it still 6?
LAST_INSERT_ID () I don't understand you.
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

Check the MySQL official documentation, it's really important ...

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

Official Documents Quote:

With no argument, last_insert_id () returns a 64-bit value representing the ' the ' automatically generated value successfull Y inserted for a auto_increment column as a result of the most recently executed INSERT statement.

Translation:

The last_insert_id() INSERT first automatically generated value of the last statement executed against the AutoIncrement column is returned with no parameters.

Official Documents Quote:

IF you insert multiple rows using a single INSERT statement, LAST_INSERT_ID () returns the value generated for the Serted row only. The reason for the "to" it possible to reproduce easily the same INSERT statement against the other server.

Translation:

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

Then, dissect

Please read the above translations in black font, bearing in mind last_insert_id() the constraints.

Why does inserting a specified value last_insert_id() invalidate it?

The official document clearly said, is the automatically generated value Ah, not the value you specify Ah, is generated by the AUTOINCREMNT counter itself can be last_insert_id() traced to wow.

Why is the value of multiple inserts, the first one is displayed, last not the last one?

Official document clearly said, is the most recent INSERT statement * * Automatically generated first value * whoa, whoa.

Summarize

Remember last_insert_id() the constraint. The INSERT first value that was automatically generated on the Autpincrement column by the most recent statement. Summed up this sentence than the translation of the words feel more comfortable

Well, the above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if you have questions you can message exchange.