您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP 呼叫 MySQL 預存程序完整指南：建立、參數傳遞、結果處理

編輯：PHP 相關知識

預存程序基礎語法

-- 建立無參數程序
CREATE PROCEDURE get_version()
BEGIN
    SELECT VERSION() AS version;
END;

-- 建立 IN 參數 (輸入)
CREATE PROCEDURE insert_user(IN p_username VARCHAR(50), IN p_password VARCHAR(100))
BEGIN
    INSERT INTO users (username, password_hash) VALUES (p_username, SHA2(p_password, 256));
END;

-- 建立 OUT 參數 (輸出)
CREATE PROCEDURE get_user_count(OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM users;
END;

-- 建立 INOUT 參數 (輸入輸出)
CREATE PROCEDURE increment_counter(INOUT p_counter INT)
BEGIN
    SET p_counter = p_counter + 1;
END;

PHP 呼叫範例 (mysqli)

<?php
$mysqli = new mysqli('localhost', 'root', 'password', 'test_db');
if ($mysqli->connect_errno) {
    die("連線失敗: " . $mysqli->connect_error);
}

// 1. 呼叫無參數程序
if ($result = $mysqli->query('CALL get_version()')) {
    $row = $result->fetch_assoc();
    echo "MySQL 版本: " . $row['version'] . "\n";
    $result->free();
    $mysqli->next_result(); // 清除多結果集
}

// 2. 呼叫 IN 參數
$username = 'john_doe';
$password = 'secret123';
$stmt = $mysqli->prepare('CALL insert_user(?, ?)');
$stmt->bind_param('ss', $username, $password);
$stmt->execute();
echo "新增使用者影響行數: " . $stmt->affected_rows . "\n";
$stmt->close();
$mysqli->next_result();

// 3. 呼叫 OUT 參數
$mysqli->query('SET @count = 0'); // 初始化使用者變數
$mysqli->query('CALL get_user_count(@count)');
$result = $mysqli->query('SELECT @count AS total');
$row = $result->fetch_assoc();
echo "使用者總數: " . $row['total'] . "\n";
$result->free();
$mysqli->next_result();

// 4. 呼叫 INOUT 參數
$counter = 5;
$mysqli->query("SET @counter = $counter");
$mysqli->query('CALL increment_counter(@counter)');
$result = $mysqli->query('SELECT @counter AS new_val');
$row = $result->fetch_assoc();
echo "計數器新值: " . $row['new_val'] . "\n"; // 6
$result->free();
$mysqli->next_result();

$mysqli->close();

PHP 呼叫範例 (PDO)

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test_db', 'root', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

// IN 參數
$stmt = $pdo->prepare('CALL insert_user(:user, :pass)');
$stmt->bindValue(':user', 'jane_doe');
$stmt->bindValue(':pass', 'password456');
$stmt->execute();

// OUT 參數：PDO 不直接支援 OUT 綁定，需用使用者變數
$pdo->exec('SET @out_count = 0');
$pdo->exec('CALL get_user_count(@out_count)');
$row = $pdo->query('SELECT @out_count AS total')->fetch(PDO::FETCH_ASSOC);
echo "總數: " . $row['total'];

// 多結果集處理 (SELECT 回傳多個結果集)
$stmt = $pdo->prepare('CALL complex_proc()');
$stmt->execute();
do {
    if ($stmt->columnCount() > 0) {
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            print_r($row);
        }
    }
} while ($stmt->nextRowset());

關鍵注意事項

議題	說明	解決方案
多結果集	CALL 可能產生多個結果集 (SELECT、狀態)	mysqli: `next_result()`；PDO: `nextRowset()`
OUT/INOUT 參數	PHP 驅動不直接支援綁定 OUT	使用 MySQL 使用者變數 `@var` 中轉
權限	需 `EXECUTE` 權限	`GRANT EXECUTE ON PROCEDURE db.proc TO 'user'@'host';`
DELIMITER	建立程序時需變更分隔符	客戶端工具 (Workbench、命令列) 自動處理；PHP 中執行 DDL 可用 `$$` 或自定義
SQL 注入	預存程序內部動態 SQL 仍有風險	程序內部亦使用參數化、避免 `PREPARE ... FROM @sql`

效能與架構考量

優點：減少網路往返、封裝業務邏輯、權限控制細緻、減少應用層程式碼
缺點：除錯困難、版本控制弱、資料庫綁定、增加 DB CPU 負載、測試隔離難
建議：核心高頻、多應用共用、涉及跨表複雜交易的邏輯適合用預存程序；單純 CRUD 留在應用層

完整資料表建立腳本 (供測試)

CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_db;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash CHAR(64) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DELIMITER $$

CREATE PROCEDURE get_version()
BEGIN
    SELECT VERSION() AS version;
END$$

CREATE PROCEDURE insert_user(IN p_username VARCHAR(50), IN p_password VARCHAR(100))
BEGIN
    INSERT INTO users (username, password_hash) VALUES (p_username, SHA2(p_password, 256));
END$$

CREATE PROCEDURE get_user_count(OUT p_count INT)
BEGIN
    SELECT COUNT(*) INTO p_count FROM users;
END$$

CREATE PROCEDURE increment_counter(INOUT p_counter INT)
BEGIN
    SET p_counter = p_counter + 1;
END$$

DELIMITER ;