PHP 呼叫 MySQL 預存程序完整指南：mysqli 與多結果集處理

背景

MySQL 5.0+ 支援預存程序。PHP 舊版 `mysql` 擴展不支援返回結果集的預存程序，必須改用 `mysqli` 或 `PDO`。

基本呼叫方式 (mysqli)

<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'database');
if ($mysqli->connect_errno) die('連線失敗: ' . $mysqli->connect_error);

// 呼叫無參數預存程序
$result = $mysqli->query("CALL get_all_users()");
if ($result) {
    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }
    $result->free();
}

// 呼叫帶參數預存程序 (預防 SQL 注入)
$stmt = $mysqli->prepare("CALL get_user_by_id(?)");
$stmt->bind_param('i', $userId);
$userId = 123;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) { /* ... */ }
$stmt->close();
?>

處理 OUT/INOUT 參數

<?php
// 預存程序：CREATE PROCEDURE get_total(IN category_id INT, OUT total INT)
$categoryId = 5;
$mysqli->query("SET @total = 0");
$mysqli->query("CALL get_total($categoryId, @total)");
$result = $mysqli->query("SELECT @total AS total");
$row = $result->fetch_assoc();
echo "總數: " . $row['total'];
?>

多重結果集處理 (關鍵技巧)

預存程序若包含多個 `SELECT`，需使用 `mysqli::next_result()` 迭代：

<?php
// 預存程序返回兩個結果集：用戶列表 + 統計摘要
if ($mysqli->multi_query("CALL get_users_with_stats()")) {
    do {
        if ($result = $mysqli->store_result()) {
            // 處理當前結果集
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
    } while ($mysqli->next_result());
}
?>

解決「Commands out of sync」錯誤

連續呼叫多個預存程序時，若未正確釋放結果集會發生此錯誤。解決方案：

1. 每次呼叫後完整處理所有結果集 (`next_result()` 迴圈)
2. 或重新建立連線 (效能較差)

<?php
// 正確模式：完整消耗結果集
function callProc($mysqli, $sql) {
    if (!$mysqli->multi_query($sql)) return false;
    do {
        if ($res = $mysqli->store_result()) {
            $data = $res->fetch_all(MYSQLI_ASSOC);
            $res->free();
            return $data;
        }
    } while ($mysqli->next_result());
    return [];
}

// 依序呼叫多個預存程序
$users = callProc($mysqli, "CALL get_users()");
$stats = callProc($mysqli, "CALL get_stats()");
?>

PDO 替代方案

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("CALL get_user(?)");
$stmt->execute([123]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 處理多結果集
do {
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // ...
} while ($stmt->nextRowset());
?>

最佳實踐總結

• 優先使用 mysqli prepared statements 防止注入
• 預存程序回傳結果集務必使用 multi_query + next_result()
• OUT 參數透過 Session 變數 (`@var`) 取得
• 錯誤處理：檢查 `$mysqli->errno`、`$stmt->errno`
• 考慮使用 PDO 統一資料庫存取介面