Yii2 批量插入數據：batchInsert 高性能寫入與模型驗證結合實戰

Yii2 批量插入數據：batchInsert 高性能寫入與模型驗證結合實戰

循環調用 ActiveRecord::save() 執行 N 條 INSERT 是性能殺手。Yii2 提供 yii\db\Command::batchInsert() 直接構建批量 INSERT 語句，單次 SQL 寫入成百上千條記錄，性能提升 10-50 倍。本文詳解 batchInsert 用法、AR 模型驗證結合、分批提交、事務控制與避坑指南。

一、基礎用法：構建純數組批量寫入

<?php
// 目標表：test (id PK, name VARCHAR, status TINYINT, created_at INT)

// 1. 準備列名 (不含自增 id)
$columns = ['name', 'status', 'created_at'];

// 2. 準備行數據 (二維數組，順序對應 $columns)
$rows = [
    ['張三', 1, time()],
    ['李四', 1, time()],
    ['王五', 0, time()],
    // ... 成百上千行
];

// 3. 執行批量插入
$affected = Yii::$app->db->createCommand()
    ->batchInsert('test', $columns, $rows)
    ->execute();

echo "成功插入 $affected 行";

二、進階：結合 ActiveRecord 模型驗證

直接寫入繞過 AR 驗證有風險。正確模式：模型實例化 → validate() → 提取屬性 → batchInsert。

<?php
use yii\helpers\ArrayHelper;

// 假設 $models 是 Post 模型實例數組 (來自表單批量提交、Excel 導入等)
$validRows = [];
foreach ($models as $model) {
    // 1. 執行驗證規則 (required, string, integer, unique 等)
    if ($model->validate()) {
        // 2. 提取屬性 (只取表字段，過濾臨時字段)
        $validRows[] = $model->getAttributes(['title', 'content', 'author_id', 'status']);
    } else {
        // 記錄驗證失敗詳情
        $errors[] = ['model' => $model, 'errors' => $model->getErrors()];
    }
}

if (!empty($validRows)) {
    // 3. 批量寫入 (使用模型 tableName() 與 attributes() 保持一致)
    $columns = array_keys($validRows[0]); // ['title', 'content', 'author_id', 'status']
    
    Yii::$app->db->createCommand()
        ->batchInsert(Post::tableName(), $columns, $validRows)
        ->execute();
}

// 處理錯誤反饋
if (!empty($errors)) {
    // 返回前端顯示具體哪行哪字段驗證失敗
}

三、大數據量分批提交 (防止 SQL 過大、內存溢出、鎖表過久)

<?php
function batchInsertChunked(string $table, array $columns, array $rows, int $batchSize = 500): int
{
    $totalInserted = 0;
    $db = Yii::$app->db;
    
    // 可選：開啟事務 (視業務是否要求原子性)
    $transaction = $db->beginTransaction();
    try {
        foreach (array_chunk($rows, $batchSize) as $chunk) {
            $db->createCommand()
                ->batchInsert($table, $columns, $chunk)
                ->execute();
            $totalInserted += count($chunk);
        }
        $transaction->commit();
    } catch (\Exception $e) {
        $transaction->rollBack();
        throw $e;
    }
    
    return $totalInserted;
}

// 使用
$rows = [...] ; // 10000 行
$inserted = batchInsertChunked('test', ['name', 'status'], $rows, 1000);

四、獲取自增 ID (MySQL 8.0+ / PostgreSQL RETURNING)

MySQL：batchInsert 返回影響行數，無法直接獲取自增 ID 序列。方案：

1. 預生成 ID：使用 Snowflake/UUID 作為主鍵，應用層生成，寫入時包含 ID 列。
2. LAST_INSERT_ID() + 連續假設：僅在無併發插入、自增步長為 1 時可用，不推薦生產依賴。
3. RETURNING (MySQL 8.0.27+ / MariaDB 10.5+)：INSERT ... RETURNING id，Yii2 尚未原生封裝，需手寫 SQL。

// PostgreSQL 原生支持 RETURNING
$sql = "INSERT INTO test (name, status) VALUES " . 
       implode(',', array_fill(0, count($rows), '(?,?)')) . 
       " RETURNING id";
$ids = Yii::$app->db->createCommand($sql, array_merge(...$rows))->queryColumn();

五、ON DUPLICATE KEY UPDATE (MySQL 覆蓋/更新)

Yii2 batchInsert 不直接支持 ON DUPLICATE KEY UPDATE。需手構建 SQL 或使用 yii\db\mysql\QueryBuilder::buildBatchInsert() 獲取基礎 SQL 後拼接：

$command = Yii::$app->db->createCommand();
$sql = $command->getSql() . ' ON DUPLICATE KEY UPDATE `status`=VALUES(`status`), `updated_at`=VALUES(`updated_at`)';
// 注意：getSql() 為 protected，需反射或複製 QueryBuilder 邏輯
// 更簡單：手寫完整 SQL 執行

六、性能對比與最佳實踐總結

七、避坑檢查清單

1. 字段順序嚴格對應：$columns 與 $rows 每行元素順序必須完全一致。
2. 類型轉換：布爾值、日期時間、枚舉需轉為數據庫接受的純量值 (int/string)。
3. 特殊字符轉義：batchInsert 內部使用參數綁定，自動防注入，無需手動轉義。
4. 主鍵衝突：若表有自增主鍵，$columns 不要包含主鍵列。
5. 事務邊界：批次過大導致長事務鎖表，建議 500-2000 行/批次。
6. 內存控制：$rows 數組過大佔用 PHP 內存，生產環境配合 array_chunk 或生成器分批構建。

總結

Yii2 batchInsert 是批量寫入的「核武器」：單條 SQL、參數綁定安全、性能極致。生產環境鐵律：模型驗證先行 → 提取純屬性數組 → 分批事務提交 → 異常回滾記錄。結合 array_chunk、預生成 ID、RETURNING 新特性，可優雅解決從導入 Excel 到日志归档的各類批量寫入需求。