LAST_INSERT_ID()可以用来查询当前线程最后插入记录使用的值。
mysql> create table test_last_insert_id
-> (i smallint not null auto_increment,
-> name varchar(10),primary key(i))
-> engine=innodb;
Query OK, 0 rows affected (0.01 sec)注意：
mysql> insert into test_last_insert_id values(1,’1′);
Query OK, 1 row affected (0.00 sec)mysql> select last_insert_id(); –按理应该显示1的
+——————+
| last_insert_id() |
+——————+
| 0 |
+——————+
1 row in set (0.00 sec)mysql> insert into test_last_insert_id values(2,’2′);
Query OK, 1 row affected (0.01 sec)mysql> insert into test_last_insert_id values(3,’3′);
Query OK, 1 row affected (0.00 sec)mysql> insert into test_last_insert_id values(4,’4′);
Query OK, 1 row affected (0.00 sec)mysql> select last_insert_id();
+——————+
| last_insert_id() |
+——————+
| 0 | –显示都示0
+——————+
1 row in set (0.00 sec)mysql> insert into test_last_insert_id values(null,’5′);
Query OK, 1 row affected (0.00 sec)mysql> select last_insert_id();
+——————+
| last_insert_id() |
+——————+
| 5 | –这里显示正常
+——————+
1 row in set (0.00 sec)mysql> insert into test_last_insert_id values(0,’0′);
Query OK, 1 row affected (0.00 sec)mysql> select last_insert_id();
+——————+
| last_insert_id() |
+——————+
| 6 | –这里显示正常
+——————+
1 row in set (0.00 sec)
总结：如果指定auto_increment的列插入的值大于等于1的值，显示为是最后插入记录使用的值（这里不考虑连续插入insert into table_name values((?,?),(?,?),(?,?))），如果是0或null，则显示为0