Method for returning an auto-incremental ID after mysql inserts data

After mysql inserts data, it returns an auto-incremental ID. The difference between mysql and oracle is that oracle supports sequence id, and mysql itself has a column that can be used as an auto-increment field, after inserting a piece of data, how can mysql obtain the auto-increment id value? Method 1: Use last_insert_id 1 mysql> SELECT LAST_INSERT_ID (); the generated ID is saved on the server after each connection. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value of the latest statement that affects the AUTO_INCREMENT column generated by the client. This value cannot be affected by other clients, even if they generate their own AUTO_INCREMENT values. This behavior ensures that you can retrieve your ID without worrying about the activity of other clients and does not need to be locked or processed. Each mysql_query operation can be understood as an "Atomic" Operation on the mysql server. Write operations often require table locking. The mysql Application Server lock table is not our application lock table. It is worth noting that if you insert multiple records at a time, this function returns the ID value of the first record. 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. 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. The second method is to use max (id) to use last_insert_id as the basic connection, if a window is called, 10 is always returned. If it is not inserted frequently, we can use this method to obtain the returned id value 1 select max (id) from user; the disadvantage of this method is that it is not suitable for high concurrency. The returned values may be inaccurate at the same time. Method 3: Create a stored procedure, operation 1 DELIMITER $2 drop procedure if exists 'test' $3 create definer = 'root' @ 'localhost' PROCEDURE 'test '(in name varchar (100 ), out oid int) 4BEGIN5 insert into user (loginname) values (name); 6 select max (id) from user into oid; 7 select oid; 8END $9 DELIMITER; 1 call test ('gg ', @ id ); method 4: Use @ identity 1 select @ IDENTITY @ identity to indicate the last time the request was sent to an auto-incrementing column with the identity attribute) is defined by the system. Global variable. 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. Method 5: Use getGeneratedKeys () 01 Connection conn =; 02 03 Serializable ret = null; 04 PreparedStatement state = .; 05 ResultSet rs = null; 06try {07 state.exe cuteUpdate (); 08 rs = state. getGeneratedKeys (); 09 if (rs. next () {10 ret = (Serializable) rs. getObject (1); 11} 12} catch (SQLException e) {13} 14 return ret; To sum up, after inserting data in mysql, it is easy to get the id in high concurrency. In addition, although the last_insert_id is session-based, it does not know why the test was not successful. In fact, you can use the selectkey node in the ibtias framework and set the insert return value type to integer to return this id value.