How to obtain the newly inserted id value in the sqlserver/MySQL database

How to obtain the newly inserted id value in the sqlserver/MySQL database

In the sqlserver Database

In actual database applications, we often need to get the inserted flag value to write data into the relevant table. But what we usually get is the value we need?
(1) Sometimes we use select @ identity to obtain the value we just inserted, such as the following code

Code 1:
Use tempdb
If exists (select * From SYS. objects where object_id = object_id (n' [test1] ') and type in (n'u '))
Drop table [test1]
Go
Create Table test1
(
Id int identity (1, 1 ),
Content nvarchar (1, 100)
)
Insert into test1 (content) values ('solorez ')
Select @ identity

In optimistic situations, this is fine, but if we first run the following code 2 to create a trigger and then run code 3:

Code 2:
Create Table Test2
(
Id int identity (100,1 ),
Content nvarchar (1, 100)
)

Create trigger tri_test1_identitytest_ I
On test1 after insert
As
Begin
Insert into Test2
Select Content from inserted
End

Code 3:
Insert into test1 (content) values ('lorez2 ')
Select @ identity
 
We can see that the resulting id value is more than 100. Obviously, this is the ID value generated in Table Test2, and it is no longer what we want.
Let's take a look at the definition of @ identity: Identity
Originally,@ Identity returns the id value inserted at the end of the current transaction.Because after the execution of the insert test1 table, the trigger is triggered and the insert Test2 table is triggered, the value of @ identity is the ID value generated by the table test2.

(2) In this case, we may use the following method:

Code 4:
Insert into test1 (content) values ('lorez3 ')
Select ident_current ('test1 ')

It seems that the result is still correct, but if we run code 4 multiple times and run the following code 5 at the same time:

Code 5:
Insert into test1 (content) values ('lorez3 ')

Waitfor delay '00: 00: 20'
Select ident_current ('test1 ')
 
The results are not what we want!
Let's take a look at the definition of ident_current (tablename:Ident_current (tablename)
Returns the final id value of the specified table.

(3) It is time to show the answer. We can use the following code:

Code 6:
Insert into test1 (content) values ('lorez3 ')
Select scope_identity ()

At this time, whether we add a trigger or run parallel insert, we always get the id value of the current transaction.

Scope_identity () Definition:Scope_identity () returns the latest Identifier value generated for the current session and a table in the current scope.

 

Differences between the three functions:

Ident_current returns the latest id value generated for a session and a specified table in the field.

@ Identity returns the latest id value generated for a table in the current session across all scopes.

Scope_identity returns the latest id value generated for the current session and a table in the current scope.

 

 

In the MySQL database

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:

(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.

You can use select last_insert_id (); to query the value of last_insert_id.

Insert multiple records using a single insert statement. last_insert_id only returns the value of the first inserted record.

(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 the connection is not closed when select @ identity is executed after the insert operation. Otherwise, the value is null.