萬盛學電腦網

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

PHP 調用 MySQL 存儲過程完整實戰:參數綁定、結果集處理與事務控制

為什麼使用存儲過程?

  • 性能:減少網絡往返、預編譯緩存、批量操作原子化
  • 安全:權限控制 (只授予 EXECUTE 權限)、防 SQL 注入參數化
  • 維護:業務邏輯下沉、版本獨立部署、跨語言複用
  • 事務:複雜多表操作原子性保證

MySQLi 面向對象完整範例

1. 建立測試存儲過程

DELIMITER //

-- 無參查詢
CREATE PROCEDURE sp_get_active_users()
BEGIN
    SELECT id, username, email, created_at 
    FROM users 
    WHERE status = 1 
    ORDER BY created_at DESC;
END //

-- IN 參數
CREATE PROCEDURE sp_get_user_by_id(IN p_user_id INT)
BEGIN
    SELECT * FROM users WHERE id = p_user_id;
END //

-- OUT 參數
CREATE PROCEDURE sp_count_users_by_status(IN p_status TINYINT, OUT p_total INT)
BEGIN
    SELECT COUNT(*) INTO p_total FROM users WHERE status = p_status;
END //

-- INOUT 參數
CREATE PROCEDURE sp_increment_counter(INOUT p_counter INT, IN p_step INT)
BEGIN
    SET p_counter = p_counter + p_step;
END //

-- 多結果集
CREATE PROCEDURE sp_user_with_orders(IN p_user_id INT)
BEGIN
    SELECT * FROM users WHERE id = p_user_id;
    SELECT * FROM orders WHERE user_id = p_user_id ORDER BY created_at DESC;
END //

-- 事務內操作
CREATE PROCEDURE sp_transfer_balance(
    IN p_from_id INT, 
    IN p_to_id INT, 
    IN p_amount DECIMAL(10,2),
    OUT p_success BOOL,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_from_balance DECIMAL(10,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = '轉賬失敗:數據庫異常';
    END;
    
    START TRANSACTION;
    
    SELECT balance INTO v_from_balance FROM accounts WHERE user_id = p_from_id FOR UPDATE;
    
    IF v_from_balance < p_amount THEN
        SET p_success = FALSE;
        SET p_message = '餘額不足';
        ROLLBACK;
    ELSE
        UPDATE accounts SET balance = balance - p_amount WHERE user_id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE user_id = p_to_id;
        INSERT INTO transfers (from_id, to_id, amount, created_at) VALUES (p_from_id, p_to_id, p_amount, NOW());
        SET p_success = TRUE;
        SET p_message = '轉賬成功';
        COMMIT;
    END IF;
END //

DELIMITER ;

2. PHP 調用封裝類

<?php
declare(strict_types=1);

namespace App\Database;

use mysqli;
use mysqli_result;
use Exception;

class StoredProcedureExecutor
{
    private mysqli $mysqli;
    
    public function __construct(mysqli $mysqli)
    {
        $this->mysqli = $mysqli;
        $this->mysqli->set_charset('utf8mb4');
    }
    
    /**
     * 執行存儲過程,自動處理多結果集
     * @return array [結果集1, 結果集2, ...] 每個結果集為關聯數組數組
     */
    public function execute(string $procName, array $params = []): array
    {
        // 構建 CALL 語句
        $placeholders = str_repeat('?,', count($params));
        $placeholders = rtrim($placeholders, ',');
        $sql = "CALL `$procName`($placeholders)";
        
        $stmt = $this->mysqli->prepare($sql);
        if (!$stmt) {
            throw new Exception("Prepare 失敗: " . $this->mysqli->error);
        }
        
        // 綁定參數
        if ($params) {
            $types = $this->getTypesString($params);
            $stmt->bind_param($types, ...$params);
        }
        
        if (!$stmt->execute()) {
            throw new Exception("執行失敗 [$procName]: " . $stmt->error);
        }
        
        // 處理多結果集
        $results = [];
        do {
            $result = $stmt->get_result();
            if ($result) {
                $rows = [];
                while ($row = $result->fetch_assoc()) {
                    $rows[] = $row;
                }
                $results[] = $rows;
                $result->free();
            }
        } while ($this->mysqli->more_results() && $stmt->next_result());
        
        $stmt->close();
        return $results;
    }
    
    /**
     * 執行帶 OUT/INOUT 參數的存儲過程
     * @return array ['results' => [...], 'out_params' => [...]]
     */
    public function executeWithOutParams(string $procName, array &$params): array
    {
        // 此處需用戶自行管理 OUT 參數變量引用
        // 建議:OUT 參數用單獨查詢 SELECT @out_param 獲取
        // 或使用 PDO (支援 bindParam by reference)
        throw new Exception("建議使用 PDO 處理 OUT 參數,見下方示例");
    }
    
    private function getTypesString(array $params): string
    {
        $types = '';
        foreach ($params as $param) {
            if (is_int($param)) $types .= 'i';
            elseif (is_float($param)) $types .= 'd';
            elseif (is_string($param)) $types .= 's';
            elseif (is_bool($param)) $types .= 'i';
            elseif (is_null($param)) $types .= 's'; // NULL 當字串處理
            else $types .= 's';
        }
        return $types;
    }
}

3. PDO 版本 (更優雅處理 OUT 參數)

<?php
declare(strict_types=1);

namespace App\Database;

use PDO;
use PDOStatement;
use Exception;

class PdoStoredProcedure
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    public function call(string $procName, array $params = []): array
    {
        $placeholders = str_repeat('?,', count($params));
        $placeholders = rtrim($placeholders, ',');
        $sql = "CALL `$procName`($placeholders)";
        
        $stmt = $this->pdo->prepare($sql);
        foreach ($params as $i => $param) {
            $stmt->bindValue($i + 1, $param, $this->getPdoType($param));
        }
        
        $stmt->execute();
        
        $results = [];
        do {
            if ($stmt->columnCount() > 0) {
                $results[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
            }
        } while ($stmt->nextRowset());
        
        return $results;
    }
    
    /**
     * 處理 OUT/INOUT 參數
     * $params 中 OUT 參數需預先定義變量引用
     */
    public function callWithOutParams(string $procName, array &$params): array
    {
        $placeholders = [];
        $bindParams = [];
        
        foreach ($params as $name => &$value) {
            $placeholders[] = '?';
            $bindParams[$name] = &$value; // 保持引用
        }
        
        $sql = "CALL `$procName`(" . implode(',', $placeholders) . ")";
        $stmt = $this->pdo->prepare($sql);
        
        foreach ($bindParams as $i => &$value) {
            $stmt->bindParam($i + 1, $value, $this->getPdoType($value), 64); // 64 = max length for OUT
        }
        
        $stmt->execute();
        
        $results = [];
        do {
            if ($stmt->columnCount() > 0) {
                $results[] = $stmt->fetchAll(PDO::FETCH_ASSOC);
            }
        } while ($stmt->nextRowset());
        
        return $results;
    }
    
    private function getPdoType($value): int
    {
        if (is_int($value)) return PDO::PARAM_INT;
        if (is_bool($value)) return PDO::PARAM_BOOL;
        if (is_null($value)) return PDO::PARAM_NULL;
        return PDO::PARAM_STR;
    }
}

// 使用示例
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$executor = new PdoStoredProcedure($pdo);

// IN 參數
$users = $executor->call('sp_get_user_by_id', [123]);
print_r($users[0] ?? []);

// OUT 參數
$status = 1;
$total = 0; // 將被修改
$executor->callWithOutParams('sp_count_users_by_status', [
    'p_status' => $status,
    'p_total' => &$total,  // 傳引用
]);
echo "總數: $total\n";

// INOUT 參數
$counter = 5;
$executor->callWithOutParams('sp_increment_counter', [
    'p_counter' => &$counter,
    'p_step' => 3,
]);
echo "計數器: $counter\n"; // 8

事務邊界管理

⚠️ 存儲過程內部事務與 PHP 端事務不要混用:

  • 方案 A:業務邏輯全在存儲過程,PHP 只調用 CALL,不開啟事務
  • 方案 B:業務邏輯在 PHP,存儲過程只做單表原子操作,PHP 控制事務
  • 方案 C:PHP 開啟事務 → 調用多個存儲過程 → 提交/回滾 (需存儲過程不自行 COMMIT/ROLLBACK)

效能優化建議

  • 避免在循環中調用存儲過程,改為批量參數或臨時表
  • 存儲過程內使用 FOR UPDATE 鎖行、適當索引
  • 結果集大時分頁或游標處理,避免一次性載入
  • 監控 SHOW PROCESSLIST 發現長時間運行的存儲過程

常見錯誤排查

錯誤原因解決
Commands out of sync上一個結果集未完全讀取/釋放循環 next_result() 直到 false,或用 mysqli::multi_query
OUT 參數為 NULLMySQLi 不直接支援 OUT 參數綁定改用 PDO bindParam 引用、或額外 SELECT @out_var
PROCEDURE 不存在數據庫、Schema、大小寫不匹配檢查 SHOW PROCEDURE STATUS WHERE Db='dbname'
權限拒絕用戶無 EXECUTE 權限GRANT EXECUTE ON PROCEDURE db.sp_name TO 'user'@'host'
copyright © 萬盛學電腦網 all rights reserved