mysql中last_insert_id()用法
前言
在使用 MySQL 时，若表中含自增字段（auto_increment 类型），则向表中 insert 一条记录后，可以调用 last_insert_id() 来获得最近 insert 的那行记录的自增字段值。但事实上，使用 last_insert_id() 时有很多注意事项，很容易踩到坑。
数据准备
CREATE TABLE `tb_product` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) DEFAULT NULL COMMENT '商品名称',
`stock` int DEFAULT NULL COMMENT '库存量',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
注意事项
事项一
last_insert_id() 的值是由 MySQL server 来维护的，而且是为每个连接维护独立的值，也就是说，某个连接调用 last_insert_id() 获取到的值是这个连接最近一次 insert 执行后的自增值，该值不会被其它连接所影响。这个行为保证了不同的连接能正确地获取到它最近一次 insert 执行所插入的行的自增值，也就是说，last_insert_id() 的值不需要通过加锁或事务机制来保证其在多连接场景下的正确性。
@PostMapping("testMysql3")
public Integer testMysql3(String name, int stock) {
jdbcTemplate.update("insert into tb_product(name,stock) values(?,?)", name, stock);
return jdbcTemplate.queryForObject("select last_insert_id()", Integer.class);
}
我们在 mysql 客户端如 阿里云dms 上是没办法得到正确的 last_insert_id() 返回值的，因为 insert 语句和 select 语句 是两个连接执行的。
事项二
若在 SQL 中显式指定自增字段的值，last_insert_id() 获取到的值为 0。也就是说，只有自增字段由 mysql 来分配时，last_insert_id() 才能得到正确的值，SQL中显式更新自增字段值时，last_insert_id() 返回的值不可用。
@PostMapping("testMysql4")
public Integer testMysql4(int id, String name, int stock) {
jdbcTemplate.update("insert into tb_product(id,name,stock) values(?,?,?)", id, name, stock);
return jdbcTemplate.queryForObject("select last_insert_id()", Integer.class); // 结果为0
}
事项三
若在同一条 insert 语句中插入多行，last_insert_id() 返回的值只是自增一次的值，这与实际情况不符（表中的实际情况是自增字段值在旧值基础上加N）。
@PostMapping("testMysql5")
public Integer testMysql5(String name, int stock) {
jdbcTemplate.update("insert into tb_product(name,stock) values(?,?),(?,?),(?,?)", name, stock, name, stock, name, stock);
return jdbcTemplate.queryForObject("select last_insert_id()", Integer.class);
}
事项四
若调用 last_insert_id() 时传入了参数，则它会将参数值返回给调用者，并记住这个值，下次调用不带参数的 last_insert_id() 时，仍会返回这个值。可以利用这个特性实现一个多用户安全的全局计数器。
@PostMapping("testMysql6")
public Integer testMysql6(int id, int stock) {
jdbcTemplate.update("update tb_product set stock=last_insert_id(stock+?) where id=?", stock, id);
return jdbcTemplate.queryForObject("select last_insert_id()", Integer.class);
}
假设 stock 原来为 10，在原来的基础上增加 10，接下来的 last_insert_id() 就返回 20。
