How to get last insert auto increment id.

Published on| February 10th, 2010 | No Comment.

Categories:Command Help | Tags: AUTO_INCREMENT, last_insert_id, MySQL

If you set ‘AUTO_INCREMENT’ to attribute of the table column , its column will increment automatic when records is inserted into the table. And its increment number is unique. Moreover, I think that you often specify such information for primary key (PRIMARY KEY).

You will not consider this column information usually. However, when you want to take out inserted information, you might feel it to the necessity.

How to get last insert auto increment id?
It is easy. If you execute the following SQL, MySQL will answer last insert auto increment id.

select last_insert_id() [from table name];

> mysql -h=xxx -u=username -p=**** dbname
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 465
Server version: 5.1.36-community MySQL Community Server (GPL)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE TABLE `test_autoincrement` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `value` text NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into test_autoincrement(value) values('sample-data...');
Query OK, 1 row affected (0.02 sec)
 
mysql> select last_insert_id() from test_autoincrement;
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.02 sec)
 
mysql> insert into test_autoincrement(value) values('sample-data2...');
Query OK, 1 row affected (0.03 sec)
 
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)
 
mysql>