Auto-increment ID of MYSQL

When you insert A piece of data in Table A and insert data in Table B, you need to add the auto-increment corresponding to the corresponding field in table A. How do you get the auto-increment of Table? Next, we will introduce the auto-increment in MySQL. MYSQL obtains the auto-incremental ID in four ways. 1. selectmax (id) fromtablename2.SELECTLAST _ INSERT_ID () function LAST_INSERT_ I

When you insert A piece of data in Table A and insert data in Table B, you need to add the auto-increment corresponding to the corresponding field in table A. How do you get the auto-increment of Table? Next, we will introduce the auto-increment in MySQL. Four ways for MYSQL to obtain auto-incremental IDs 1. select max (ID) from tablename 2. SELECT LAST_INSERT_ID () function LAST_INSERT_ I

When you insert A piece of data in Table A and insert data in Table B, you need to add auto-increment values for the corresponding fields in table A. How do you get the auto-increment values in Table? Next, we will introduce the auto-increment in MySQL.

Four Methods for MYSQL to obtain auto-increment IDS

1. select max (id) from tablename

2. SELECT LAST_INSERT_ID () function

LAST_INSERT_ID is table-independent. If you insert data to table a and then insert data to table B, LAST_INSERT_ID will change.

Max (id) is obviously unavailable when multiple users insert data alternately. In this case, LAST_INSERT_ID should be used, 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. This value cannot be affected by connections of other clients. This ensures that you can retrieve your own ID without worrying about the activity of other clients and do not need to lock the client. INSERT multiple records using a single INSERT statement. LAST_INSERT_ID returns a list.

3. select @ IDENTITY;

@ Identity refers to the value of the auto-incrementing column corresponding to the last time data is inserted into a table with the identity attribute (that is, the auto-incrementing column). It is a global variable defined by the system. Generally, global variables defined by the system start with @ and User-Defined variables start.

For example, if the auto-increment column of Table A is id, after A row of data is inserted into Table A, if the value of the auto-increment column is automatically increased to 101 after the data is inserted, the value obtained through select @ identity is 101. The premise of using @ identity is that the connection is not closed when select @ identity is executed after the insert operation. Otherwise, the value is NULL.

4. show table status;

In the result, the corresponding table name record contains an Auto_increment field. The value of the next auto-increment ID is the maximum auto-increment ID of the current table.

@ Identity and LAST_INSERT_ID () are different. The chm help of mysql5.5 is described as follows:

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

Let's take a look:
Last_insert_id

The value to be returned from LAST_INSERT_ID (). this is stored in the binary log when you use LAST_INSERT_ID () in a statement that updates a table. setting this variable does not update the value returned by the mysql_insert_id () c api function.

@ Identity is the synonym of LAST_INSERT_ID (). There is no big difference, but let's continue to understand LAST_INSERT_ID.

Last_insert_id () New Understanding

Official Description: The ID that was generated is maintained in the server on a per-connection basis. this means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. this value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. this behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without 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,
It is based on the last insert statement executed between a single connection client, and there is no impact between clients. It is a connection-level function and only valid for the current user's connection.

In MySQL, The auto_increment type id field is used as the table's primary key.
The general practice is to use the "select max (id) from tablename" method, but obviously this method needs to consider concurrency and "X lock" must be applied to the master table in the transaction. unlock after obtaining the value of max (id.
This method requires a lot of steps, it is a little troublesome, And the concurrency is not good. Is there a simpler way? One of the answers is through the select LAST_INSERT_ID () operation.
At first glance, it is very similar to select max (id), but in fact it is thread-safe. That is to say, it is specific to database connection. The following is an experiment description:
(1) Insert A record to Table A in connection 1. Table A contains an auto_increment field.
(2) Insert another record to Table A in connection 2.
(3) result: the result of executing select in connection 1 is different from the result of executing select LAST_INSERT_ID () in connection 2; the results of executing select max (id) in the two connections are the same. LAST_INSERT_ID ()
In MSSQL, the difference between SCOPE_IDENTITY () and IDENT_CURRENT () is similar. Use SCOPE_IDENTITY () to obtain the current session value of an IDENTITY field, and use IDENT_CURRENT () to obtain the maximum value inserted on an IDENTITY field without distinguishing sessions.

Conclusion: @ identity is the synonym of LAST_INSERT_ID (), but @ identity is a system-level variable, while LAST_INSERT_ID () is a user variable (connection-level variable), which is more secure, we recommend that you use LAST_INSERT_ID ()

Reference URL:

Four ways for MYSQL to get auto-incremental ID: http://www.2cto.com/database/201304/199707.html

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 Reference Manual: http://www.yesky.com/imagesnew/software/mysql/manual_toc.html