Obtain auto-increment primary keys in MySQL

When writing a database program, we often need to obtain the maximum number of serial numbers in a table. Generally, we need to obtain the id of the inserted data and use selectmax (id) fromtabl.

When writing a database program, we often need to obtain the maximum number of sequence numbers in a table. Generally, we need to obtain the id of the data just inserted and use select max (id) from tabl

When writing a database program, we often need to obtain the maximum number of numbers in a table,
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.

The following three methods are described:

(1) getGeneratedKeys () method:
Program snippets:
Connection conn =;
Serializable ret = null;
PreparedStatement state = .;
ResultSet rs = null;
Try {
State.exe cuteUpdate ();
Rs = state. getGeneratedKeys ();
If (rs. next ()){
Ret = (Serializable) rs. getObject (1 );
}
} Catch (SQLException e ){
}
Return ret;

(2) LAST_INSERT_ID:
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.
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 * of 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:
String SQL = "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 after the insert operation, the connection is not closed when the select @ identity is executed. Otherwise, the value is NULL.