Mysql last_insert_id () The function of the use of the detailed _mysql

Recently and Sobin are doing a project for a boutique course, because a fixed ID is used as an association between tables, so after inserting data into the previous table, you pass the ID of the inserted data generation to the next table. Studied a decision to use MySQL to provide a last_insert_id () function.

Copy Code code as follows:

LAST_INSERT_ID () (with no argument) returns the automatically generated value, is set for a auto_increment col Umn by the most recently executed inserts or UPDATE statement to affect such a column. For example, after inserting a row that generates a Auto_increment value, you can get the value like this:
Mysql> SELECT last_insert_id ();
-> 195

Simply put, this function will return the value of the field that the inserted record added to the table, and we generally name the self-added field as an ID. This allows you to return the ID value of the record you just inserted.

A simple example:

Copy Code code as follows:

$query = "INSERT into ' testtable ' (' clou1 ', ' clou2 ') VALUES (' TestValue ', ' Test ')";
mysql_query ($query);
$query = "Select last_insert_id ()";
$result =mysql_query ($query);
$rows =mysql_fetch_row ($result);
echo $rows [0];

This function is based on connection, which is not affected by the connection of other clients, so the result is accurate. If you use the Select Max (ID) from table, it is possible to go wrong under a high density insert request, returning an error value

LAST_INSERT_ID description

As can be seen from the name, last_insert_id is the last inserted ID value, according to the official MySQL manual, it has 2 ways to use

One is without parameters: last_insert_id (), this method is used with the Auto_increment property, and when the record is added to the table with the ' Auto_increment ' property field, last_insert_id () returns the value of the field. Everyone can try (I have verified);
The second is an expression: as described above last_insert_id (value+1), it returns the value of the expression, that is ' value+1 ';
##################################
LAST_INSERT_ID () automatically returns the value of the first table in the Auto_increment column setting in the last INSERT or UPDATE query.

Considerations for MySQL's last_insert_id:

First, the connection object used by the query and insert must be the same, or the return value is unpredictable.

Mysql> SELECT last_insert_id ();

-> 100

The value returned using this function to a given connection object is the first auto_increment value of the connection object that produces the most recent statement that affects the Auto_increment column. This value cannot be affected by other connection objects, that is, they produce their own auto_increment values.

Second, last_insert_id is not table-Independent, if you insert data into table A, and then insert data into form B, LAST_INSERT_ID returns the ID value in table B.

Third, if you use an INSERT statement to insert multiple rows, last_insert_id () returns only the value produced when the first row of data is inserted. The reason for this is that it makes it easy to rely on other servers to replicate the same INSERT statement.

Mysql> INSERT into T VALUES

-> (null, ' Mary '), (null, ' Jane '), (null, ' Lisa ');

Mysql> SELECT * from T;

| ID | name |

+--+--+

| 1 | Bob |

| 2 | Mary |

| 3 | Jane |

| 4 | Lisa |

Mysql> SELECT last_insert_id (); This is the key question that I want to explain.

| last_insert_id () |

| 2 |

Although 3 new rows are inserted into T, the ID of the first row of the rows is 2, which is also the value returned by last_insert_id ().

The above mentioned is the entire content of this article, I hope you can enjoy.

Please take a moment to share the article with your friends or leave a comment. We will sincerely thank you for your support!