ThinkPHP 批量更新數據三大方案：CASE WHEN、迴圈事務、遞歸回滾實戰

ThinkPHP 批量更新數據三大方案：CASE WHEN、迴圈事務、遞歸回滾實戰

批量更新是後台管理系統高頻需求（如批量修改會員等級、調整商品排序）。本文匯總三種方案：單條 SQL CASE WHEN（最高效）、迴圈事務（簡單可控）、遞歸回滾重試（部分失敗自動重試），並給出完整可用代碼。

方案一：單條 SQL CASE WHEN（最高效，推薦大量資料）

原理：利用 MySQL CASE WHEN 在一條 UPDATE 中完成多條記錄的不同值更新。

// 構建 SQL 範例
UPDATE `categories` SET
    `display_order` = CASE `id`
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    `title` = CASE `id`
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE `id` IN (1,2,3);

PHP 通用構建函數

function buildBatchUpdateSql($table, $rows, $pk = 'id') {
    // $rows = [ ['id'=>1, 'name'=>'A', 'sort'=>10], ['id'=>2, 'name'=>'B', 'sort'=>20] ]
    if (!$rows) return '';
    $fields = array_keys($rows[0]);
    $pkIndex = array_search($pk, $fields);
    if ($pkIndex === false) throw new Exception("資料缺少主鍵 {$pk}");
    unset($fields[$pkIndex]);

    $cases = [];
    $ids = [];
    foreach ($rows as $row) {
        $ids[] = $row[$pk];
        foreach ($fields as $field) {
            $cases[$field][] = "WHEN {$row[$pk]} THEN '" . addslashes($row[$field]) . "'";
        }
    }
    $setClauses = [];
    foreach ($cases as $field => $whens) {
        $setClauses[] = "`{$field}` = CASE `{$pk}` " . implode(' ', $whens) . " END";
    }
    $sql = "UPDATE `{$table}` SET " . implode(', ', $setClauses) . " WHERE `{$pk}` IN (" . implode(',', $ids) . ")";
    return $sql;
}

// 使用
$sql = buildBatchUpdateSql('users', [
    ['id' => 1, 'name' => 'Tom', 'status' => 1],
    ['id' => 2, 'name' => 'Jerry', 'status' => 0],
]);
M()->execute($sql); // ThinkPHP 執行原生 SQL

方案二：迴圈事務（簡單、適合中小量、邏輯複雜）

public function batchUpdateLoop($table, $rows, $pk = 'id') {
    $model = M($table);
    $model->startTrans();
    try {
        foreach ($rows as $row) {
            $id = $row[$pk];
            unset($row[$pk]);
            if (!$model->where([$pk => $id])->save($row)) {
                throw new Exception("ID {$id} 更新失敗");
            }
        }
        $model->commit();
        return true;
    } catch (Exception $e) {
        $model->rollback();
        return false;
    }
}

方案三：遞歸回滾重試（部分失敗自動剔除重試，極度健壯）

/**
 * @param array $wherePk  ['id' => [1,2,3]]
 * @param array $dataRows [ ['name'=>'A'], ['name'=>'B'], ['name'=>'C'] ]
 * @return array|false  成功返回更新成功的主鍵陣列，失敗返回 false
 */
public function saveAll($wherePk, &$dataRows, $table) {
    $pk = array_keys($wherePk)[0];
    $ids = $wherePk[$pk];
    $model = M($table);

    $model->startTrans();
    $failedIndices = [];
    foreach ($ids as $i => $id) {
        if (!$model->where([$pk => $id])->save($dataRows[$i])) {
            $failedIndices[] = $i;
        }
    }

    if (empty($failedIndices)) {
        $model->commit();
        return $wherePk; // 全部成功
    }

    // 部分失敗：回滾、剔除失敗項、遞歸重試成功項
    $model->rollback();
    foreach (array_reverse($failedIndices) as $idx) {
        unset($ids[$idx], $dataRows[$idx]);
    }
    $wherePk[$pk] = array_values($ids);
    $dataRows = array_values($dataRows);

    if (empty($ids)) return false; // 全失敗
    return $this->saveAll($wherePk, $dataRows, $table); // 遞歸重試剩餘
}

// 測試調用
public function test() {
    $where = ['ID' => [70,73,74,80,83]];
    $data = [
        ['School'=>'DK Univ 01','isExport'=>0],
        ['School'=>'DK Univ 02','isExport'=>0],
        ['School'=>'DK Univ 03','isExport'=>0],
        ['School'=>'DK Univ 04','isExport'=>0],
        ['School'=>'','isExport'=>0], // 故意造錯
    ];
    $result = $this->saveAll($where, $data, 'want');
    if ($result) {
        echo "成功更新 ID: " . implode(',', $result['ID']);
    } else {
        echo "全部失敗";
    }
}

四、方案選型指南

五、注意事項

• 主鍵必須存在： 所有方案依賴主鍵定位記錄。
• 欄位白名單： 方案二/三使用模型 save() 會自動過濾非資料表欄位；方案一需自行過濾。
• 事務隔離級別： 高並發下建議 REPEATABLE READ（預設）或 SERIALIZABLE，防止幻讀。
• SQL 長度限制： 方案一單條 SQL 可能超過 max_allowed_packet，超大批次需分批。