萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 獲取 INSERT 自增 ID:mysql_insert_id() vs LAST_INSERT_ID()

兩種方式對比

特性mysql_insert_id() (C API / PHP 函數)LAST_INSERT_ID() (SQL 函數)
調用層級客戶端庫函數,無網絡往返SQL 語句,需發送/解析/執行
作用域當前連接(Session)當前連接(Session)
返回類型長整型 (PHP int)BIGINT (64位)
多行 INSERT返回第一行自增值返回第一行自增值
隱式提交影響不受影響不受影響
BIGINT 自增可能溢出(PHP int 32位)安全返回 64 位值

PHP 使用示例

mysqli(推薦)

$mysqli = new mysqli("localhost", "user", "pass", "db");
$mysqli->query("INSERT INTO users (name) VALUES ('Alice')");
$new_id = $mysqli->insert_id;  // 直接屬性,最快
echo "New ID: $new_id";

PDO

$pdo = new PDO("mysql:host=localhost;dbname=db", "user", "pass");
$pdo->exec("INSERT INTO users (name) VALUES ('Bob')");
$new_id = $pdo->lastInsertId();  // 返回字符串,大數安全
echo "New ID: $new_id";

舊 mysql_* 擴展(已棄用)

mysql_query("INSERT INTO users (name) VALUES ('Charlie')");
$new_id = mysql_insert_id();  // 依賴最後打開的連接
// 不推薦使用

SQL 層面使用 LAST_INSERT_ID()

基本用法

INSERT INTO t (name) VALUES ('test');
SELECT LAST_INSERT_ID();  -- 返回自增值

多行插入

INSERT INTO t (name) VALUES ('a'), ('b'), ('c');
SELECT LAST_INSERT_ID();  -- 返回第一行 ID (假設起始 100 → 返回 100)
-- 後續行 ID 為 101, 102(連續)

配合 ON DUPLICATE KEY UPDATE 保持語義

INSERT INTO t (id, name) VALUES (1, 'x') 
ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id), name = 'y';
SELECT LAST_INSERT_ID();  -- 返回受影響行的自增 ID(新插入或更新的行)

此技巧使 LAST_INSERT_ID() 在「更新」時也返回該行 ID,便於應用層統一邏輯。

事務與並發安全性

  • 自增值在 INSERT 執行瞬間分配,事務回滾不回收(可能出現間隙)
  • 並發事務各自獲得唯一自增值,無競爭
  • innodb_autoinc_lock_mode 影響並發性能:
    • 0:傳統鎖(表級自增鎖),安全但慢
    • 1:連續模式(默認),批量插入表鎖,單條無鎖
    • 2:交錯模式,最高並發,但主從復制需 ROW 格式

避坑指南

  1. 不要在 SELECT 後調用:SELECT * FROM t; SELECT LAST_INSERT_ID(); 返回上一次 INSERT 的值,非當前查詢
  2. 不要在存儲過程中混用:過程內 INSERT 後,外層調用 mysql_insert_id() 可能取不到值,統一用 SELECT LAST_INSERT_ID() INTO @var
  3. BIGINT 自增必須用 PDO/LAST_INSERT_ID():PHP int 在 32 位系統溢出
  4. REPLACE INTO 視同 DELETE + INSERT:會產生新自增值,舊值丟失
  5. INSERT IGNORE 重複鍵不插入:mysql_insert_id() 返回 0,LAST_INSERT_ID() 保持不變

最佳實踐

-- 應用層統一封裝
function insertAndGetId($pdo, $table, $data) {
    $cols = implode(',', array_keys($data));
    $ph = ':' . implode(',:', array_keys($data));
    $sql = "INSERT INTO `$table` ($cols) VALUES ($ph)";
    $pdo->prepare($sql)->execute($data);
    return $pdo->lastInsertId();  // 字符串,安全
}

-- 如需在同一事務中多次插入並獲取各 ID
$pdo->beginTransaction();
$id1 = insertAndGetId($pdo, 't1', ['name'=>'a']);
$id2 = insertAndGetId($pdo, 't2', ['name'=>'b']);
$pdo->commit();
copyright © 萬盛學電腦網 all rights reserved