MYSQL数据库查询自增ID LAST_INSERT_ID

一、建表准备

drop table if exists Student1;
create table Student1(
id int NOT NULL KEY auto_increment,
`when` varchar(21)
);

drop table if exists Student2;
create table Student2(
id int NOT NULL KEY auto_increment,
`when` varchar(21)
);

insert Student1(id,`when`) values(NULL, 'now');
insert Student1(id,`when`) values(NULL, 'now');

insert Student2(id,`when`) values(NULL, 'now');
insert Student2(id,`when`) values(NULL, 'now');
insert Student2(id,`when`) values(NULL, 'now');

mysql> select * from Student1;
+----+------+
| id | when |
+----+------+
|  1 | now  |
|  2 | now  |
+----+------+

mysql> select * from Student2;
+----+------+
| id | when |
+----+------+
|  1 | now  |
|  2 | now  |
|  3 | now  |
+----+------+

二、查询自增ID(下一次数据插入时使用该值)

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student1';
+----------------+
| auto_increment |
+----------------+
|              3 |
+----------------+

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+

三、查询LAST_INSERT_ID

select LAST_INSERT_ID()，用于获取上次使用自增方式插入记录的自增ID。

1 LAST_INSERT_ID是针对一个连接，LAST_INSERT_ID不会被其它连接的数据插入影响。

2 连接建立时LAST_INSERT_ID是初始值0。

3 自增方式插入记录会修改LAST_INSERT_ID值。

4 非自增方式插入记录不会修改LAST_INSERT_ID值。

5 事务回滚不会修改LAST_INSERT_ID值。

6 一条插入语句插入多条记录，LAST_INSERT_ID值是第一条插入记录值。

四、数据操作对自增ID的影响

1 数据插入影响自增ID

2 数据删除不影响自增ID

3 事务回滚不影响自增ID

4 重启mysqld不影响自增ID

delete from Student2 where id=1;

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+

delete from Student2 where id=2;

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|              4 |
+----------------+

insert Student2(id,`when`) values(10, 'now');

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|             11 |
+----------------+

delete from Student2 where id=10;

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|             11 |
+----------------+

insert Student2(id,`when`) values(2, 'now');

SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='test' AND TABLE_NAME='Student2';
+----------------+
| auto_increment |
+----------------+
|             11 |
+----------------+