Three methods to insert a record into mysql and return the record id

Three methods to return the record id after inserting a record in the mysql tutorial

Method 1

Id int (11) not null pri key auto_increment, name varchar (12), backup varchar (50)
Now, when you want to insert a record, return its id value (only the values of the name and backup fields are inserted at the time of insertion ). How can I write this statement. Thank you!

You are more worried than you are. No need to lock the table, the returned ID must be yours, based on the current connection session

Automatically returns the first occurrence value set for the last INSERT or UPDATE query as the AUTO_INCREMENT column.

Mysql> SELECT LAST_INSERT_ID ();

-> 195

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.
 

In the source code of mysql, mysql_insert_id is defined in this way.
My_ulonglong STDCALL mysql_insert_id (MYSQL * mysql)
{
Return mysql->; last_used_con->; insert_id;
}

The APIS provided by MYSQL to c ++ and php tutorials generally have a MYSQL struct.
The struct contains the insert_id and insert_id types my_ulonglong. Long.

Each mysql_query operation can be understood as an "Atomic" Operation on the mysql server. Write operations in database tutorials often require table locking. The mysql Application Server lock table is not our application lock table.

 

Method 2

Mysql provides a LAST_INSERT_ID () function.

LAST_INSERT_ID () (with no argument) returns the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT or UPDATE statement to affect such a column. for example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this:

Mysql> SELECT LAST_INSERT_ID ();
-> 195 simply put, this function will return the value of the field that is automatically added to the inserted record in the table. Generally, we name the auto-increment field as ID. In this way, the ID value of the newly inserted record can be returned.

A simple example:

$ Query = "insert into 'testtable' ('clou1', 'clou2') VALUES ('testvalue', 'test ')";
Mysql_query ($ query );
$ Query = "SELECT LAST_INSERT_ID ()";
$ Result = mysql_query ($ query );
$ Rows = mysql_fetch_row ($ result );
Echo $ rows [0]; to return the ID of the newly inserted record.
It is worth noting that if you insert multiple records at a time, this function returns the ID value of the first record.

Mysql> insert into t VALUES
-> (NULL, 'Mary '), (NULL, 'Jane'), (NULL, 'lisa ');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

Mysql> SELECT * FROM t;
+ ---- + ------ +
| Id | name |
+ ---- + ------ +
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+ ---- + ------ +
4 rows in set (0.01 sec)

Mysql> SELECT LAST_INSERT_ID ();
+ ------------------ +
| LAST_INSERT_ID () |
+ ------------------ +
| 2 |
+ ------------------ +
The 1 row in set (0.00 sec) function is based on connection, that is, it will not be affected by the connection of other clients, so the result is accurate. If select max (id) from table is used, a high-density insert request may cause a problem and an error value is returned.

Method 3

. Select max (id) from user;
2. select last_insert_id () as id from user limit 1;
(The returned id for this test is always 0, which is a problem)
3. Storage Process
1)
Using el
Create sequence seqID
Minvalue 1
Max value 999999999999999999999999999
Start with 1
Increment by 1
Nocache
Order;
Create or replace procedure sp_insert (aName int, rst out int) is
Begin
Insert into tablename (id, name) values (seqID. nextval, aName );
Rst: = seqID. currval;
End;
2) implementation in mysql
DELIMITER $
Drop procedure if exists 'test' $
Create definer = 'root' @ 'localhost' PROCEDURE 'test' (in name varchar (100), out oid int)
BEGIN
Insert into user (loginname) values (name );
Select max (id) from user into oid;
Select oid;
END $
DELIMITER;
Then execute
Call test ('gg ', @ id );
Returns the id ......