萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP mysqli 多查詢批次執行:multi_query、store_result、next_result 完整實戰指南

單次資料庫往返執行多條 SQL,能大幅降低網路延遲開銷。mysqli::multi_query() 配合 store_result()、next_result()、more_results() 實現批次查詢與混合 DML/DDL 執行。本文詳細解析 API 行為、結果集處理流程、陷阱與最佳實踐。

核心 API 行為詳解

方法返回值作用關鍵行為
multi_query(string $sql)bool發送一或多條 SQL (分號分隔)非阻塞發送,**不返回結果集**,僅指示首條語句是否發送成功
store_result()mysqli_result|false從伺服器讀取**當前**結果集到客戶端緩衝區必須針對**每個**產生結果集的語句 (SELECT/SHOW/EXPLAIN) 呼叫一次
use_result()mysqli_result|false逐行讀取 (非緩衝)大結果集串流處理,但阻塞連線,不可再發新查詢
next_result()bool推進到下一個結果集讀取伺服器狀態包 (OK/ERR/EOF),**必須呼叫**才能處理下一條語句
more_results()bool是否還有結果集待處理配合 do-while 迴圈使用

標準處理流程模板

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
if ($mysqli->connect_errno) die("Connect failed: {$mysqli->connect_error}");

$sql = "SELECT * FROM news LIMIT 10; SELECT * FROM users; SELECT COUNT(*) FROM logs;";

if ($mysqli->multi_query($sql)) {
    do {
        // 1. 嘗試獲取結果集 (SELECT/SHOW/EXPLAIN 會有)
        if ($result = $mysqli->store_result()) {
            // 處理結果集
            while ($row = $result->fetch_assoc()) {
                // 處理每行...
                print_r($row);
            }
            $result->free(); // 釋放記憶體
        } else {
            // 2. 無結果集 (INSERT/UPDATE/DELETE/DDL) 或錯誤
            if ($mysqli->errno) {
                echo "Query error: {$mysqli->error}\n";
            } else {
                echo "Affected rows: {$mysqli->affected_rows}\n";
            }
        }
        
        // 3. 分隔輸出 (可選)
        if ($mysqli->more_results()) {
            echo "--- Next result set ---\n";
        }
    } while ($mysqli->next_result()); // 4. 推進到下一個結果
} else {
    echo "Multi query failed: {$mysqli->error}";
}

$mysqli->close();

關鍵陷阱與避坑指南

陷阱 1:忘記處理所有結果集 → 連線污染

若 multi_query 包含 3 條 SELECT,但迴圈只處理 1 次就跳出,**剩餘 2 個結果集留在伺服器緩衝區**,下次查詢會讀到舊資料導致「Commands out of sync」錯誤。

// ✅ 必須完整消費所有結果集
do {
    if ($result = $mysqli->store_result()) {
        $result->free();
    }
} while ($mysqli->next_result());

陷阱 2:混合 DML 與 SELECT 時的 affected_rows

multi_query($sql);
do {
    if ($result = $mysqli->store_result()) {
        // SELECT 結果
        $result->free();
    } else {
        // UPDATE 結果:affected_rows 可用
        echo "Updated: {$mysqli->affected_rows}\n";
    }
} while ($mysqli->next_result());

陷阱 3:大結果集記憶體爆炸

store_result() 將**整個結果集載入 PHP 記憶體**。百萬行資料會 OOM。

// ✅ 大結果集使用非緩衝讀取
if ($result = $mysqli->use_result()) { // 或 MYSQLI_USE_RESULT
    while ($row = $result->fetch_assoc()) {
        // 逐行處理,記憶體恆定
    }
    $result->free();
} while ($mysqli->next_result());
// ⚠️ 使用 use_result 期間無法在同連線執行其他查詢

陷阱 4:預處理語句不支援 multi_query

mysqli::prepare() 單條 SQL,不可包含多條語句。需用 multi_query 直接拼接 SQL 字串,**極度注意 SQL 注入**,參數化無效。

// ❌ 危險:直接拼接用戶輸入
$ids = implode(',', $_POST['ids']); // 未驗證!
$mysqli->multi_query("DELETE FROM t1 WHERE id IN ($ids); DELETE FROM t2 WHERE id IN ($ids);");

// ✅ 安全:驗證後拼接,或迴圈執行預處理
$ids = array_map('intval', $_POST['ids']);
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $mysqli->prepare("DELETE FROM t1 WHERE id IN ($placeholders)");
$stmt->bind_param(str_repeat('i', count($ids)), ...$ids);
$stmt->execute();
// 第二條同理...

實戰場景:批次匯入/同步/報表聚合

<?php
function batchSync(PDO $source, mysqli $target, array $tables): void {
    foreach ($tables as $table) {
        // 1. 獲取來源資料 (PDO 游標分批)
        $stmt = $source->prepare("SELECT * FROM {$table} WHERE updated_at > ? ORDER BY id LIMIT 1000");
        $stmt->execute([$lastSyncTime]);
        
        // 2. 組裝批次 INSERT ... ON DUPLICATE KEY UPDATE
        $columns = ['id', 'name', 'email', 'updated_at']; // 實際應動態獲取
        $placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
        $onDuplicate = implode(', ', array_map(fn($c) => "{$c}=VALUES({$c})", $columns));
        
        $sql = "INSERT INTO {$table} (" . implode(',', $columns) . ") VALUES ";
        $batchSql = [];
        $params = [];
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $batchSql[] = $placeholders;
            $params = array_merge($params, array_values($row));
        }
        
        if (empty($batchSql)) continue;
        
        $sql .= implode(', ', $batchSql) . " ON DUPLICATE KEY UPDATE {$onDuplicate}";
        
        // 3. 執行 (單條大 SQL 比 multi_query 更穩定、可參數化)
        $targetStmt = $target->prepare($sql);
        // 動態綁定參數...
        $targetStmt->execute();
    }
}

效能對比:multi_query vs 迴圈單查 vs 批次 INSERT

方式網路往返SQL 注入風險記憶體適用場景
迴圈單查N 次低 (可預處理)低少量、邏輯複雜
multi_query1 次高 (難參數化)高 (store_result)異構多語句、DDL 批次
批次 INSERT (VALUES ..., ...)1 次低 (可預處理)中大量同表寫入
LOAD DATA INFILE1 次低極低 (串流)超大量匯入 (百萬行+)

結語

multi_query 是雙面刃:減少網路往返但引入結果集處理複雜度、SQL 注入風險、記憶體壓力。現代最佳實踐:

  1. 讀取操作:單查詢 + 索引優化,或分頁游標
  2. 寫入操作:批次 INSERT ... VALUES (),(),() ON DUPLICATE KEY UPDATE + 預處理
  3. DDL/維護腳本:multi_query 可接受,但務必完整消費所有結果集
  4. 異構微服務聚合:應用層組合 (BFF) 或 GraphQL,而非資料庫層面 multi_query

記住:正確性 > 效能 > 便利性。除非經過嚴格基準測試證明必要,否則優先選擇參數化單查詢或批次 INSERT。

copyright © 萬盛學電腦網 all rights reserved