Last_insert_id usage of MySQL

Source: Internet
Author: User

Last_insert_id () automatically returns the value of the first table set in the auto_increment column in The Last insert or update query.

Notes for last_insert_id of MySQL:

FirstThe connection object used for query and insertion must be the same. Otherwise, the return value is unpredictable.

Mysql> select last_insert_id ();

-> 100

Using this function, the value returned to a given connection object is the first auto_increment value of the latest statement that affects the auto_increment column of the connection object. This value cannot be affected by other connection objects, that is, they generate their own auto_increment values.

SecondLast_insert_id is not related to table. If you insert data to table A and then insert data to table B, last_insert_id returns the id value in table B.

ThirdIf you use an insert statement to insert multiple rows, last_insert_id () Only returns the value generated when inserting the first row of data. This makes it easy to copy the same insert Statement on other servers.

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 issue to be explained.

| Last_insert_id () |

| 2 |

Although 3 new rows are inserted into t, the ID generated for the first row of these rows is 2, which is also the value returned by last_insert_id.

FourthIf you use insert ignore and the record is ignored, the auto_increment counter does not increment, and last_insert_id () returns 0, which indicates that no record is inserted.

Generally, you can obtain the ID of the inserted data by using select max (ID) from table. But in the case of multiple threads, it won't work. Max (ID) is obviously unavailable when multiple users insert data alternately. This should use last_insert_id, because last_insert_id is based on connection, as long as each thread uses an independent connection object, the last_insert_id function returns the ID of the first record generated by the connection for the latest insert or update operation on the auto_increment column. Last_insert_id is based on a single connection,
Cannot be changed by other client connections.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.