PHP MySQL 非緩衝查詢完整指南：mysqli、PDO、mysql 三種驅動實現

前言

默認情況下，PHP MySQL 查詢會將**整個結果集**一次性拉取到 PHP 內存（緩衝查詢）。面對百萬行大數據導出、流式處理、內存受限場景，**非緩衝查詢**可逐行獲取，內存佔用恆定。本文詳解 mysqli、PDO、mysql (已棄用) 三種驅動的非緩衝查詢用法、適用場景與避坑指南。

一、緩衝 vs 非緩衝 核心差異

二、mysqli 非緩衝查詢

$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');

// 關鍵：MYSQLI_USE_RESULT
$result = $mysqli->query("SELECT id, name, email FROM users WHERE status=1", MYSQLI_USE_RESULT);

if ($result) {
    // 逐行處理，內存恆定
    while ($row = $result->fetch_assoc()) {
        // 處理邏輯：寫 CSV、推隊列、發郵件...
        fputcsv($fp, $row);
    }
    $result->free(); // 必須釋放，否則該連接無法執行下一條查詢
}

// 同一連接再次查詢前，必須確保上一個非緩衝結果集已 free
$mysqli->query("UPDATE stats SET exported=1 WHERE date=CURDATE()");

mysqli 關鍵點

• `MYSQLI_USE_RESULT` vs `MYSQLI_STORE_RESULT` (默認緩衝)
• `$result->free()` **強制釋放**，否則後續查詢報 `Commands out of sync`
• 預處理語句同理：`$stmt->execute()` 後 `$stmt->store_result()` 為緩衝，不調用則為非緩衝

三、PDO 非緩衝查詢

$pdo = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // 使用原生預處理
    // 關鍵屬性：禁用緩衝
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
]);

$stmt = $pdo->query("SELECT id, name, data FROM large_table");
/* 或預處理
$stmt = $pdo->prepare("SELECT ... WHERE id > ?");
$stmt->execute([$lastId]);
*/

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 流式處理
    processRow($row);
}

// 關閉游標，釋放連接
$stmt->closeCursor();

PDO 關鍵點

• `PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false` 必須在構造函數選項或 `setAttribute` 中設置
• 預處理語句默認非緩衝（不調用 `store_result`），但建議顯式設置屬性確保一致性
• `$stmt->closeCursor()` 相當於 `free()`，允許同連接執行下一條

四、mysql (ext/mysql) 非緩衝查詢【已棄用，僅維護老項目參考】

$conn = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db', $conn);
mysql_set_charset('utf8', $conn);

// mysql_unbuffered_query 返回資源
$result = mysql_unbuffered_query("SELECT * FROM huge_logs");

if ($result) {
    while ($row = mysql_fetch_assoc($result)) {
        // 處理
    }
    mysql_free_result($result); // 必須釋放
}

警告：`ext/mysql` 自 PHP 5.5 棄用，PHP 7.0 移除。老項目**必須遷移至 mysqli/PDO**。

五、實戰場景：百萬行導出 CSV 內存對比

// 測試表：100 萬行，每行 ~200 字節
// PHP memory_limit = 128M

// ❌ 緩衝查詢：內存峰值 ~200MB → Fatal error: Allowed memory size exhausted
$result = $pdo->query("SELECT * FROM big_table");
$rows = $result->fetchAll(); // 一次性載入
foreach ($rows as $row) fputcsv($fp, $row);

// ✅ 非緩衝查詢：內存峰值 ~2MB（僅單行 + 輸出緩衝）
$stmt = $pdo->query("SELECT * FROM big_table", PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($fp, $row);
}
$stmt->closeCursor();

六、進階：非緩衝查詢 + 生成器 封裝迭代器

function unbufferedQuery(PDO $pdo, string $sql, array $params = []): Generator {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    try {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            yield $row;
        }
    } finally {
        $stmt->closeCursor(); // 確保釋放
    }
}

// 使用：極其優雅的內存安全遍歷
foreach (unbufferedQuery($pdo, "SELECT * FROM logs WHERE created_at > ?", [$since]) as $row) {
    // 處理邏輯
    // 即使 break 也會執行 finally 釋放游標
}

七、避坑清單

八、總結

• **mysqli**：`MYSQLI_USE_RESULT` + `free()`
• **PDO**：`MYSQL_ATTR_USE_BUFFERED_QUERY => false` + `closeCursor()`
• **核心價值**：恆定微小內存處理任意大小結果集，支撐導出、ETL、流式計算
• **代價**：放棄 `num_rows`、雙向游標、並發查詢，需嚴格管理資源釋放
• **最佳實踐**：封裝為 Generator 迭代器，業務代碼零感知內存安全

掌握非緩衝查詢，是 PHP 開發者從「增刪改查」進階到「大數據處理、高併發架構」的必經之路。