Examples of MySQL last_insert_id usage

Environment: MySQL Sever 5.1 + MySQL command line tool

First look at an example (the primary key is self-growing):

mysql> INSERT INTO BankAccount (name,balance) VALUES (' 123 ', 1000); Query OK, 1 row affected (0.06 sec) mysql> INSERT into bankstatement (action, Txdate, AMT, TOACCNO, FROMACCNO)  
VALUES (' 122 ', curdate (), 1000, 1, 2);  
Query OK, 1 row Affected (0.00 sec) mysql> Select last_insert_id (); +------------------+  
|  
last_insert_id () |                +------------------+  
|  
7 |  
+------------------+ 1 row in Set (0.00 sec) mysql> select * from BankAccount; +-------+------+---------+  
| Accno | name |  
Balance |     +-------+------+---------+  
| 1 |     John |  
200 |     | 2 |     Dick |  
900 |     | 3 |    123 |  
1000 |     | 4 |    123 |  
1000 |  
+-------+------+---------+ 4 rows in Set (0.00 sec) mysql> select * from Bankstatement; +----+--------------+------------+------+---------+-----------+  
| ID | Action | Txdate | AMT | Toaccno |  
Fromaccno | +----+--------------+------------+------+---------+-----------+  
| 1 | Account Opening |  2012-10-14 |    100 |         NULL |  
1 |  | 2 | Account Opening | 2012-10-14 |    1000 |         NULL |  
2 |  | 3 | Find account Information |    2012-10-14 |    0 |         NULL |  
2 |  | 4 | Find account Information |    2012-10-14 |    0 |         NULL |  
1 |  | 5 | Transfer |  2012-10-14 |       100 |         1 |  
2 |  | 6 | 122 | 2012-10-14 |       1000 |         1 |  
2 |  | 7 | 122 | 2012-10-14 |       1000 |         1 |  
2 | +----+--------------+------------+------+---------+-----------+ 7 rows in Set (0.00 sec)

Summary: last_insert_id () returns the value of the Auto_increment column in the last INSERT or UPDATE statement.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/