Three kinds of return MySQL insert a record return the record ID method

Three kinds of return to MySQL tutorial insert a record to return the record ID method

Method One

ID Int (one) not null PRI key auto_increment,name varchar (a), backup varchar (50)
Now, when you want to insert a record, return his ID value (insert only the value of the name and backup field). How should I write this statement? Thank you!

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

Automatically returns the first occurrence of the last insert or UPDATE query set for the Auto_increment column.

Mysql> SELECT last_insert_id ();

-> 195

The resulting IDs are 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 for the most recent statement that affects 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 the activities of other clients and that you do not need to lock or handle it.

MySQL in the source code, MYSQL_INSERT_ID is so defined
My_ulonglong stdcall mysql_insert_id (MySQL *mysql)
{
Return mysql->;last_used_con->;insert_id;
}

MySQL provided to the c++,php tutorial, such as the API general, there is a MySQL structure.
The structure has insert_id, insert_id type My_ulonglong. It's actually long long.

Each mysql_query operation on the MySQL server can be understood as an "atomic" operation, database tutorials often need to write the lock table, is the MySQL Application Server lock table is not our application lock table.

Method Two

MySQL provides a last_insert_id () function.

LAST_INSERT_ID () (with no argument) returns the automatically generated value, is set for a auto_increment col Umn by the most recently executed inserts or UPDATE statement to affect such a column. For example, after inserting a row that generates a Auto_increment value, you can get the value like this:

Mysql> SELECT last_insert_id ();
-> 195 to put it simply, this function returns the value of the field that was inserted in the table, and we typically name that self-added field as an ID. This allows you to return the ID value of the record you just inserted.

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]; This returns the ID value of the record you just inserted.
It is worth noting that if you insert more than one record 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 |
+------------------+
1 row in Set (0.00 sec) This function is based on connection, which is not affected by the connection of other clients, so the result is accurate. If you use the Select Max (ID) from table, it is possible to go wrong under a high density insert request, and return an error value.

Method Three

. Select Max (ID) from user;
2.select last_insert_id () as ID from user limit 1; 
(the return ID for this test has been 0, somewhat problematic)
3. Stored Procedure
1
Oracel
Create sequence seqid
MinValue 1
MaxValue 999999999999999999999999999
start with 1
Increment by 1
Noca Che
Order;
Create or replace procedure Sp_insert (aname int,rst out int) is
begin
  INSERT INTO Tablen Ame (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 ' t Est ' (in name varchar (m), out OID int)
BEGIN
  INSERT INTO user (LoginName) values (name);
  Select Ma X (ID) from the user into OID;
  Select OID;
End $$
DELIMITER
and then execute
Call Test (' GG ', @id);
Return ID ...