MySQL 存儲過程作為數據庫層面的業務邏輯封裝，能有效減少網絡傳輸、提升執行效率並增強安全性。本文將從存儲過程創建語法、參數類型、實戰案例到 PHP 調用方式進行全方位講解。
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type| 模式 | 說明 | 用途 |
|---|---|---|
| IN | 輸入參數 | 傳遞值給存儲過程，內部修改不影響外部 |
| OUT | 輸出參數 | 存儲過程執行後返回值給調用者 |
| INOUT | 輸入輸出參數 | 既可傳入值，又可返回修改後的值 |
LANGUAGE SQL：指定語言為 SQL（默認）DETERMINISTIC / NOT DETERMINISTIC：是否確定性（相同輸入必產生相同輸出）CONTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA：SQL 數據訪問特性聲明SQL SECURITY {DEFINER | INVOKER}：執行權限上下文COMMENT 'string'：備註信息DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`Id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(20) NOT NULL,
`Pwd` CHAR(32) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;DELIMITER //
CREATE PROCEDURE `insertuser`(
IN `username` VARCHAR(20),
IN `userpwd` VARCHAR(32)
)
BEGIN
INSERT INTO `user` (`Name`, `Pwd`) VALUES (username, MD5(userpwd));
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE `validateuser`(
IN `username` VARCHAR(20),
OUT `param1` CHAR(32)
)
BEGIN
SELECT `Pwd` INTO param1 FROM `user` WHERE `Name` = username;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE `modifyPwd`(
IN `username` VARCHAR(20),
IN `userpwd` VARCHAR(32)
)
BEGIN
UPDATE `user` SET `Pwd` = MD5(userpwd) WHERE `Name` = username;
END //
DELIMITER ;DELIMITER //
CREATE PROCEDURE `deleteuser`(
IN `username` VARCHAR(20)
)
BEGIN
DELETE FROM `user` WHERE `Name` = username;
END //
DELIMITER ;<?php
$mysqli = new mysqli("localhost", "root", "password", "database");
if ($mysqli->connect_errno) {
die("連接失敗: " . $mysqli->connect_error);
}
$mysqli->set_charset("utf8");
// 1. 新增用戶
$username = "testuser";
$password = "testpass";
$stmt = $mysqli->prepare("CALL insertuser(?, ?)");
$stmt->bind_param("ss", $username, $password);
$stmt->execute();
echo "新增用戶 " . ($stmt->affected_rows ? "成功" : "失敗") . "
";
$stmt->close();
// 2. 驗證用戶（獲取 OUT 參數）
$username = "testuser";
$stmt = $mysqli->prepare("CALL validateuser(?, @pwd)");
$stmt->bind_param("s", $username);
$stmt->execute();
$stmt->close();
// 獲取 OUT 參數值
$result = $mysqli->query("SELECT @pwd AS pwd");
$row = $result->fetch_assoc();
$storedPwd = $row['pwd'];
$result->free();
if ($storedPwd === md5("testpass")) {
echo "用戶驗證通過
";
} else {
echo "用戶驗證失敗
";
}
// 3. 修改密碼
$newPass = "newpass123";
$stmt = $mysqli->prepare("CALL modifyPwd(?, ?)");
$stmt->bind_param("ss", $username, $newPass);
$stmt->execute();
echo "修改密碼 " . ($stmt->affected_rows ? "成功" : "失敗") . "
";
$stmt->close();
// 4. 刪除用戶
$stmt = $mysqli->prepare("CALL deleteuser(?)");
$stmt->bind_param("s", $username);
$stmt->execute();
echo "刪除用戶 " . ($stmt->affected_rows ? "成功" : "失敗") . "
";
$stmt->close();
$mysqli->close();
?><?php
$pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8", "root", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 新增用戶
$stmt = $pdo->prepare("CALL insertuser(:username, :password)");
$stmt->execute([':username' => 'pdo_user', ':password' => 'pdo_pass']);
echo "PDO 新增: " . ($stmt->rowCount() ? "成功" : "失敗") . "
";
// 驗證用戶（PDO 不直接支援 OUT 參數，需通過用戶變量）
$pdo->exec("CALL validateuser('pdo_user', @pwd)");
$pwd = $pdo->query("SELECT @pwd")->fetchColumn();
echo "PDO 驗證: " . ($pwd === md5('pdo_pass') ? "通過" : "失敗") . "
";
$pdo = null;
?>存儲過程若包含多個 SELECT 語句，PHP 需使用 mysqli::more_results() 和 mysqli::next_result() 遍歷：
if ($mysqli->multi_query("CALL multi_result_proc()")) {
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
// 處理每個結果集
}
$result->free();
}
} while ($mysqli->more_results() && $mysqli->next_result());
}try {
$mysqli->begin_transaction();
$mysqli->query("CALL transfer_funds(1, 2, 100)");
$mysqli->commit();
} catch (Exception $e) {
$mysqli->rollback();
error_log("轉賬失敗: " . $e->getMessage());
}PREPARE 語句減少解析開銷SHOW PROCEDURE STATUS 監控調用統計MySQL 存儲過程配合 PHP 的 mysqli/PDO 擴展，能夠實現高效、安全的數據庫操作封裝。關鍵點在於：正確使用 IN/OUT/INOUT 參數模式、妥善處理 OUT 參數獲取、善用預處理語句防止 SQL 注入。雖然存儲過程增加了數據庫服務器壓力，但在高併發、大數據量場景下，其減少網絡往返、利用服務端計算資源的優勢往往超過缺點。實際項目中應根據業務複雜度、團隊技術棧、運維能力綜合評估是否採用。
