MySQL self-increment ID

when you insert a table a data, insert B table of data need to add corresponding to the corresponding field in a table of the self-increment, how will you get to the a table of self-increment? You may not know the self-increment in MySQL.

four ways MySQL gets the self-increment ID

1. Select Max (ID) from TableName

2.SELECT last_insert_id () function

LAST_INSERT_ID is not related to table, last_insert_id will change if data is inserted into table A, and then the data is injected into form B.

Max (ID) is obviously not available in the case where multiple users are alternately inserting data. It's time to use last_insert_id because last_insert_id is connection based, as long as each thread uses a separate connection object, Last_insert_ The 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. This value cannot be affected by other clients (Connection), which guarantees that you will be able to retrieve your ID without worrying about other clients ' activities and do not need to lock. Insert multiple records using a single INSERT statement, LAST_INSERT_ID returns a list.

3. SELECT @ @IDENTITY;

@ @identity is a system-defined global variable that represents the last time the value of the self-increment column for inserting data into a table with the identity attribute (that is, the self-increment column). General system-defined global variables start with @@ 开头 and user-defined variables begin with @.

For example, there is a table A, its self-increment column is an ID, when inserting a row of data into a table, if the value of the self-increment after inserting data automatically increases to 101, then the value obtained by the SELECT @ @identity is 101. The @ @identity is used only if the connection is not closed when the insert operation is executed, or a null value is obtained.

4. SHOW TABLE STATUS;

The result is a auto_increment field in the corresponding table name record, and the value of the next self-increment ID is the maximum self-increment ID of the current table.

@ @identity and last_insert_id () differencesMYSQL5.5 's CHM help says so:

Identity
This variable are a synonym for the last_insert_id variable. It exists for compatibility and other database systems. You can read their value with SELECT @ @identity, and set it using set identity.

Look again:
last_insert_id

The value to is returned from last_insert_id (). This is stored in the binary log when you use LAST_INSERT_ID () in a statement a table. Setting This variable does not update the value returned by the mysql_insert_id () C API function.

@ @identity is synonymous with last_insert_id (), there is no big difference, but continue to see the face last_insert_id () understanding.

last_insert_id () New Understanding

The official note: The ID is generated are maintained in the server on a per-connection basis. This means, the value returned by the function to a given client are the first Auto_increment value generated for most Recent statement affecting an auto_increment column by that client. This value cannot is affected by other clients, even if they generate auto_increment values of their own. This behavior ensures so each client can retrieve it own ID without concern for the activity of other clients, and with The need for locks or transactions.

The return value of the last_insert_id () function is not an INSERT statement based on the entire database.
Instead, based on the most recent INSERT statement executed between a single connection client, the client is not affected, it is a connection-level function and is valid only for the current user's connection .

In MySQL, use the ID field of the Auto_increment type as the primary key for the table.
The usual practice is through "SELECT Max (ID) from tablename" approach, but obviously this practice needs to consider concurrency, need to be in the transaction "x lock" the main table, to obtain the value of Max (ID), and then unlock.
This approach requires a lot of steps, some trouble, and a bad concurrency. Is there a simpler way to do it? One answer is through the Select last_insert_id () operation.
At first glance, it looks like select Max (ID), but it's actually thread-safe. This means that it is specific to the database connection. The following experiment shows:
(1), in connection 1 to a table insert a record, a table contains a auto_increment type of field.
(2), in connection 2 to a table and then insert a record.
(3), result: The result of executing select in connection 1 and the result of executing select last_insert_id () in connection 2 is different, while the result of executing select MAX (ID) in two connections is the same. LAST_INSERT_ID ()
In fact, the difference between scope_identity () and Ident_current () in MSSQL is similar here. Use Scope_identity () to get the value of the current session in which an identity field is inserted, and using ident_current () will get the maximum value inserted on an identity field without distinguishing between different sessions.

Conclusion: @ @identity is synonymous with last_insert_id (), but @ @identity system-level variables, and last_insert_id () is a user variable (connection level variable) that is relatively more secure . Recommended use of last_insert_id ()

Reference URL:

Four ways MySQL gets the self-increment ID: http://www.2cto.com/database/201304/199707.html

A new understanding of last_insert_id () in MySQL: http://sucre.blog.51cto.com/1084905/723808

@ @identity and last_insert_id () difference: http://bbs.csdn.net/topics/390659372

MySQL Chinese reference manual: http://www.yesky.com/imagesnew/software/mysql/manual_toc.html

MySQL self-increment ID