Method of returning the self-increment ID after MySQL inserts data

Method of returning the self-increment ID after MySQL inserts data

One of the big differences between MySQL and Oracle insertion is that the Oracle support sequence does id,mysql itself with a column that can do self-growing fields, how can mysql get the value of this self-increment ID after inserting a piece of data?

Method One: The use of last_insert_id

MySQL>SELECT last_insert_id ();

    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 that the client produces to the most recent statement affecting the Auto_increment column. This value cannot be affected by other clients, even if they produce their own auto_increment values. This behavior guarantees that you will be able to retrieve your ID without worrying about other client activities, and do not need to lock or process.
 
    each mysql_query operation on the MySQL server can be understood as an "atomic" operation, write operations often need to lock the table, is the MySQL Application Server lock table is not our application lock table.
 
    Note that if you insert more than one record at a time, the function returns the ID value of the first record.
    because last_insert_id is connection based, as long as each thread uses a separate 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 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.
    last_insert_id is not related to table, and last_insert_id changes when data is inserted into table A, and then the data is injected into form B.
 
Method Two: is using Max (ID)
 
using last_insert_id is the underlying connection, and if you change a window, the call will always return to ten
We can also use this method to get the returned ID value if it is not frequently inserted

Select Max  from user;

The disadvantage of this method is that it is not suitable for high concurrency. If you insert it at the same time, the value returned may not be accurate.

Method Three: is to create a stored procedure, called in the stored procedure to first insert and then get the maximum value of the operation

DELIMITER $$DROP PROCEDURE IF EXISTS' Test ' $$CREATEDefiner=' Root ' @ ' localhost 'PROCEDURE' Test ' (inchNamevarchar( -), Out OIDint)BEGIN  Insert  into User(LoginName)Values(name); Select Max(ID) from User  intoOID; SelectOID;END$ $DELIMITER; Call Test ('GG',@id);

Method Four: Use @ @identity

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.

Method Five: is to use Getgeneratedkeys ()

Connection Conn= ;SerializableRet= NULL; PreparedStatement State= .; ResultSet RS=NULL; try {state.executeupdate (); RS=State.getgeneratedkeys (); if(Rs.Next()) {ret=(Serializable) Rs.getobject (1); }} catch (SQLException e) {}returnRet

To summarize, it is easy to get an ID when the insertion is done in MySQL and is prone to error when high concurrency occurs. In addition LAST_INSERT_ID is based on session but does not know why the test was not successful.

This ID value can be returned by using the Selectkey node in the Ibtias framework and setting the type of the Insert return value to Integer.

Method of returning the self-increment ID after MySQL inserts data