mysql中last_insert_id()用法

strongmore發表於2024-04-19
MySqlAST

前言

在使用 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。

參考

mysql LAST_INSERT_ID 使用與注意事項

相關文章