MySQL 多表插入實戰：LAST_INSERT_ID() 獲取自增 ID 實現關聯寫入

核心場景

主表插入後，需將自增主鍵 ID 同步寫入關聯表（如：用戶主表 + 權限表、訂單主表 + 明細表）。關鍵函數：LAST_INSERT_ID()（SQL）/ mysql_insert_id()（PHP）。

PHP 完整範例（PDO 現代寫法）

<?php
// 1. 建立連線（啟用異常模式、UTF8MB4）
$dsn = "mysql:host=localhost;dbname=test;charset=utf8mb4";
$pdo = new PDO($dsn, 'charles', 'charles', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

try {
    // 2. 開啟事務（保證原子性）
    $pdo->beginTransaction();

    // 3. 插入主表 contact
    $sqlContact = "INSERT INTO contact (user_name, nom, prenom, mail, passcode)
                   VALUES (:user_name, :nom, :prenom, :mail, :passcode)";
    $stmt = $pdo->prepare($sqlContact);
    $stmt->execute([
        'user_name' => 'sa',
        'nom'       => 'se',
        'prenom'    => 'sf',
        'mail'      => 'safd@p.com',
        'passcode'  => '123',
    ]);

    // 4. 獲取自增 ID（關鍵步驟）
    $lastId = $pdo->lastInsertId();  // 返回 BIGINT 字串
    echo "Last insert ID: $lastId
";

    // 5. 插入關聯表 contactdroit
    $sqlDroit = "INSERT INTO contactdroit (contact_id, droit_id) VALUES (:cid, :did)";
    $stmt = $pdo->prepare($sqlDroit);
    $stmt->execute([
        'cid' => $lastId,
        'did' => 11,
    ]);

    // 6. 提交事務
    $pdo->commit();
    echo "Good Insertion
";
    echo "Inserted ID: $lastId";

} catch (PDOException $e) {
    $pdo->rollBack();
    die("Insert failed: " . $e->getMessage());
}
?>

關鍵函數行為詳解

常見坑點與解決

1. 返回 0 或 FALSE：前一條 SQL 非 INSERT、或無 AUTO_INCREMENT 欄位、或連線失敗
2. 多行批次插入：INSERT INTO t (c) VALUES (1),(2),(3); 返回第一行 ID，連續 ID 需自行計算
3. 觸發器干擾：觸發器內再 INSERT 會覆蓋 LAST_INSERT_ID()，需在觸發器用 SET @saved_id = LAST_INSERT_ID(); 保存
4. 複寫環境：Statement-based 複寫下 LAST_INSERT_ID() 會正確複寫；Row-based 下主從一致

進階：單條 SQL 完成多表插入（MySQL 8.0+ CTE）

WITH ins AS (
    INSERT INTO contact (user_name, nom, prenom, mail, passcode)
    VALUES ('sa', 'se', 'sf', 'safd@p.com', '123')
)
INSERT INTO contactdroit (contact_id, droit_id)
SELECT LAST_INSERT_ID(), 11;  -- 同一會話可用

限制：MySQL 不支援 RETURNING 子句（PostgreSQL/Oracle 有），CTE 寫法僅適用於單條主表插入場景。

延伸閱讀

• Mysql 臨時表的用法
• 拆表用的 MySQL 存儲過程
• 深入探討 MySQL 鎖機制