萬盛學電腦網

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

PHP 呼叫 MySQL 預存程序完整指南:mysqli 與 多重呼叫處理

前言

MySQL 5.0+ 引入預存程序,PHP 透過 MySQLi 擴展可完整支援。本文說明如何正確呼叫預存程序、處理回傳結果、解決多重呼叫問題與最佳實務。

舊版 mysql 擴展的限制

已棄用/移除: `mysql_query()` 僅支援無回傳結果的預存程序。若程序有輸出參數或結果集,會拋出錯誤:

<?php
// ❌ 舊版 mysql 擴展 - 有回傳值會報錯
$rs = mysql_query("CALL func('param')", $conn);
// 錯誤:PROCEDURE func can't return a result set in the given context
?>

正解:使用 MySQLi 擴展

基本呼叫與結果處理

<?php
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

if ($mysqli->connect_errno) {
    die("連線失敗: " . $mysqli->connect_error);
}

// 呼叫預存程序
$procName = 'get_user_info';
$param = 'john_doe';

if ($result = $mysqli->query("CALL {$procName}(?)", [$param])) {
    // 處理結果集
    while ($row = $result->fetch_assoc()) {
        print_r($row);
    }
    $result->free();
} else {
    echo "呼叫失敗: " . $mysqli->error;
}

// 關閉連線
$mysqli->close();
?>

預存程序定義範例

-- MySQL 預存程序範例
DELIMITER //

CREATE PROCEDURE get_user_info(IN p_username VARCHAR(50))
BEGIN
    SELECT id, username, email, created_at 
    FROM users 
    WHERE username = p_username;
END //

DELIMITER ;

處理 OUT/INOUT 參數

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');

// 預存程序有 OUT 參數
$stmt = $mysqli->prepare("CALL calculate_total(?, ?, @total)");
$orderId = 123;
$taxRate = 0.05;

$stmt->bind_param('id', $orderId, $taxRate);
$stmt->execute();
$stmt->close();

// 取得 OUT 參數值
$result = $mysqli->query("SELECT @total AS total_amount");
$row = $result->fetch_assoc();
echo "總金額: {$row['total_amount']}";
$result->free();
?>

多重預存程序呼叫問題與解決

問題:Commands out of sync

同一連線連續呼叫多個預存程序會出錯:

<?php
// ❌ 會失敗:Commands out of sync; you can't run this command now
$mysqli->query("CALL proc1()");
$mysqli->query("CALL proc2()"); // 錯誤!
?>

解決方案一:多結果集處理

<?php
// 正確處理多重呼叫:使用 multi_query 或逐一處理結果集
$mysqli->multi_query("CALL proc1(); CALL proc2();");

do {
    if ($result = $mysqli->store_result()) {
        // 處理第一個結果集
        while ($row = $result->fetch_assoc()) {
            // ...
        }
        $result->free();
    }
} while ($mysqli->more_results() && $mysqli->next_result());
?>

解決方案二:重新連線(簡單但效能較差)

<?php
function callProc(mysqli $mysqli, string $procSql): ?mysqli_result {
    $result = $mysqli->query($procSql);
    if ($mysqli->errno) {
        // 若發生 out of sync,重新連線重試一次
        if ($mysqli->errno === 2014) { // CR_COMMANDS_OUT_OF_SYNC
            $mysqli->close();
            $mysqli = new mysqli('localhost', 'user', 'pass', 'db');
            return $mysqli->query($procSql);
        }
        return null;
    }
    return $result;
}

// 使用
$result1 = callProc($mysqli, "CALL proc1()");
$result2 = callProc($mysqli, "CALL proc2()");
?>

解決方案三:PDO(推薦現代專案)

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

// PDO 可直接處理多重呼叫
$stmt1 = $pdo->query("CALL proc1()");
$data1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$stmt1->closeCursor();

$stmt2 = $pdo->query("CALL proc2()");
$data2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$stmt2->closeCursor();
?>

完整封裝類別範例

<?php
class StoredProcedureCaller {
    private mysqli $mysqli;
    
    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }
    
    /**
     * 呼叫預存程序並回傳所有結果集
     * @return array> 多個結果集的陣列
     */
    public function call(string $procName, array $params = []): array {
        $placeholders = implode(',', array_fill(0, count($params), '?'));
        $sql = "CALL {$procName}({$placeholders})";
        
        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new RuntimeException("Prepare 失敗: {$this->mysqli->error}");
        }
        
        if ($params) {
            $types = str_repeat('s', count($params)); // 簡化:全視為字串
            $stmt->bind_param($types, ...$params);
        }
        
        $stmt->execute();
        
        $results = [];
        do {
            if ($result = $stmt->get_result()) {
                $results[] = $result->fetch_all(MYSQLI_ASSOC);
                $result->free();
            }
        } while ($this->mysqli->more_results() && $this->mysqli->next_result());
        
        $stmt->close();
        return $results;
    }
}

// 使用
$caller = new StoredProcedureCaller($mysqli);
$results = $caller->call('get_user_stats', ['john_doe']);
print_r($results[0]); // 第一個結果集
?>

最佳實務總結

  • ✅ 使用 MySQLi 或 PDO,棄用 `mysql_*` 函式
  • ✅ 使用準備語句防止 SQL 注入
  • ✅ 正確處理多結果集(`more_results()` + `next_result()`)
  • ✅ 處理 OUT/INOUT 參數 需額外 `SELECT @param` 取值
  • ✅ 釋放結果集(`free()` / `closeCursor()`)避免記憶體洩漏
  • ✅ 設定適當的錯誤模式(例外模式)
copyright © 萬盛學電腦網 all rights reserved