MySQL 插入後取得自增 ID：LAST_INSERT_ID() 與 MAX(id) 差異與併發安全性

寫入帶 AUTO_INCREMENT 主鍵的資料表後，需立即取得新生成的 ID 供後續關聯寫入。MySQL 提供 LAST_INSERT_ID() 保證連線級隔離，避免併發下 MAX(id) 產生競態條件。

函數定義

LAST_INSERT_ID()  -- 回傳當前連線最近一次 INSERT/UPDATE 產生的第一個 AUTO_INCREMENT 值

• 連線隔離：僅對當前連線 (Connection) 有效，其他客戶端插入不影響。
• 多列插入：INSERT ... VALUES (...), (...), (...) 時，回傳 第一列 的 ID。
• 無自增列影響：若陳述式未產生自增值，函數值不變。

正確用法 (PHP 範例)

$mysqli = new mysqli('localhost', 'root', 'pwd', 'db');

$sql = "INSERT INTO `testtable` (`col1`, `col2`) VALUES ('val1', 'val2')";
$mysqli->query($sql);

$new_id = $mysqli->insert_id;  // 物件屬性，等同 SELECT LAST_INSERT_ID()
echo "新記錄 ID: $new_id";

// 後續關聯寫入
$mysqli->query("INSERT INTO `detail` (`master_id`, `info`) VALUES ($new_id, 'detail data')");

錯誤寫法與風險

-- 風險：高併發下 MAX(id) 可能返回其他連線剛插入的 ID
SELECT MAX(id) FROM testtable;

-- 風險：@@IDENTITY (SQL Server 概念) 在 MySQL 不存在
SELECT @@IDENTITY;  -- MySQL 無此變數

併發模擬驗證

-- 連線 A
INSERT INTO t (val) VALUES ('A');
SELECT LAST_INSERT_ID();  -- 返回 100

-- 連線 B (同時)
INSERT INTO t (val) VALUES ('B');
SELECT LAST_INSERT_ID();  -- 返回 101 (互不干擾)

-- 連線 A 再次查詢
SELECT LAST_INSERT_ID();  -- 仍為 100

其他語言/驅動對應