Four Methods for MYSQL to obtain auto-increment IDS

Welcome to the Linux community forum and interact with 2 million technical staff. selectmax (id) fromtablename2.SELECTLAST _ INSERT_ID () function LAST_INSERT_ID is irrelevant to table. If you insert data to table a and then insert data to table B, LAST_INSERT_ID will change. When multiple users insert data Alternately

Welcome to the Linux community forum and interact with 2 million technical staff> enter 1. select max (id) from tablename 2. the SELECT LAST_INSERT_ID () function LAST_INSERT_ID is irrelevant to table. If data is inserted to table a and then to table B, LAST_INSERT_ID changes. When multiple users insert data Alternately

Welcome to the Linux community forum and interact with 2 million technicians>

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.