萬盛學電腦網

瀏覽器 Windows 10 WPS Office 防毒軟體 資料庫 Excel 教學 檔案管理 Word 教學 網頁製作 裝機必備軟體 Linux 教學

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

MySQL 存儲過程開發實戰：從入門到進階 (含 PHP 調用)

MySQL 存儲過程核心優勢

性能：減少網絡往返，SQL 在服務端編譯緩存，批量操作極快
安全：權限控制到過程級別，防 SQL 注入，隱藏表結構
一致性：業務邏輯集中在數據庫層，多應用複用，避免邏輯分散
維護性：修改邏輯無需重新部署應用，熱更新

基礎語法速查

-- 1. 創建無參過程
DELIMITER //
CREATE PROCEDURE sp_hello()
BEGIN
    SELECT 'Hello MySQL Stored Procedure!' AS message;
END //
DELIMITER ;

-- 2. 帶參數 (IN/OUT/INOUT)
CREATE PROCEDURE sp_register(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(100),
    IN p_email VARCHAR(100),
    OUT p_user_id BIGINT,
    OUT p_result_code INT,
    OUT p_result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_count INT DEFAULT 0;
    
    -- 檢查用戶名是否存在
    SELECT COUNT(*) INTO v_count FROM users WHERE username = p_username;
    IF v_count > 0 THEN
        SET p_result_code = -1;
        SET p_result_msg = '用戶名已存在';
        SET p_user_id = 0;
    ELSE
        INSERT INTO users (username, password_hash, email, created_at)
        VALUES (p_username, SHA2(p_password, 256), p_email, NOW());
        SET p_user_id = LAST_INSERT_ID();
        SET p_result_code = 0;
        SET p_result_msg = '註冊成功';
    END IF;
END //

-- 3. 調用
CALL sp_register('test_user', 'pass123', 'test@example.com', @uid, @code, @msg);
SELECT @uid, @code, @msg;

控制流語句

-- IF-ELSEIF-ELSE
IF condition1 THEN
    -- statements
ELSEIF condition2 THEN
    -- statements
ELSE
    -- statements
END IF;

-- CASE 語句
CASE var
    WHEN 1 THEN -- statements
    WHEN 2 THEN -- statements
    ELSE -- statements
END CASE;

-- WHILE 循環
WHILE i <= 10 DO
    SET i = i + 1;
END WHILE;

-- REPEAT UNTIL
REPEAT
    SET i = i + 1;
UNTIL i > 10 END REPEAT;

-- LOOP + LEAVE (類似 break)
loop_label: LOOP
    SET i = i + 1;
    IF i > 10 THEN LEAVE loop_label; END IF;
END LOOP;

-- ITERATE (類似 continue)
loop_label: LOOP
    SET i = i + 1;
    IF i % 2 = 0 THEN ITERATE loop_label; END IF;
    -- 只處理奇數
END LOOP;

變量與游標

-- 局部變量
DECLARE v_total INT DEFAULT 0;
DECLARE v_name VARCHAR(100);
DECLARE v_cursor CURSOR FOR SELECT id, name FROM users WHERE status = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;

OPEN v_cursor;
cursor_loop: LOOP
    FETCH v_cursor INTO v_id, v_name;
    IF v_done THEN LEAVE cursor_loop; END IF;
    -- 處理邏輯
END LOOP;
CLOSE v_cursor;

動態 SQL (PREPARE/EXECUTE)

CREATE PROCEDURE sp_dynamic_query(IN p_table VARCHAR(64), IN p_where VARCHAR(200))
BEGIN
    SET @sql = CONCAT('SELECT * FROM `', p_table, '` WHERE ', p_where);
    -- 防注入：嚴格驗證 p_table 白名單，p_where 參數化
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

-- 調用
CALL sp_dynamic_query('users', 'status = 1 AND age > 18');

⚠️ 動態 SQL 安全警示

絕不直接拼接用戶輸入！必須：
1. 表名/列名白名單校驗
2. 值通過 PREPARE ... USING 參數綁定
3. 或使用 sys.quote_identifier() (8.0+) 轉義標識符

事務控制

CREATE PROCEDURE sp_transfer(
    IN p_from_id BIGINT, IN p_to_id BIGINT, IN p_amount DECIMAL(15,2),
    OUT p_code INT, OUT p_msg VARCHAR(100)
)
BEGIN
    DECLARE v_balance DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_code = -1;
        SET p_msg = '轉賬失敗：系統異常';
    END;
    
    START TRANSACTION;
    
    -- 鎖定源賬戶 (SELECT ... FOR UPDATE)
    SELECT balance INTO v_balance FROM accounts WHERE id = p_from_id FOR UPDATE;
    IF v_balance < p_amount THEN
        ROLLBACK;
        SET p_code = -2;
        SET p_msg = '餘額不足';
    ELSE
        UPDATE accounts SET balance = balance - p_amount WHERE id = p_from_id;
        UPDATE accounts SET balance = balance + p_amount WHERE id = p_to_id;
        INSERT INTO transfer_log (from_id, to_id, amount, created_at) 
        VALUES (p_from_id, p_to_id, p_amount, NOW());
        COMMIT;
        SET p_code = 0;
        SET p_msg = '轉賬成功';
    END IF;
END //

錯誤處理機制

-- 1. 捕獲特定錯誤
DECLARE CONTINUE HANDLER FOR 1062  -- Duplicate key
BEGIN
    SET p_code = -3; SET p_msg = '主鍵衝突';
END;

-- 2. 捕獲 SQLSTATE
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'  -- 完整性約束違反
BEGIN
    ROLLBACK; SET p_code = -4; SET p_msg = '數據完整性錯誤';
END;

-- 3. 通用異常處理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        @sqlstate = RETURNED_SQLSTATE,
        @errno = MYSQL_ERRNO,
        @text = MESSAGE_TEXT;
    INSERT INTO error_log (sqlstate, errno, msg, proc_name, created_at)
    VALUES (@sqlstate, @errno, @text, 'sp_transfer', NOW());
    ROLLBACK;
    SET p_code = -99; SET p_msg = '系統錯誤';
END;

PHP 調用最佳實踐 (mysqli / PDO)

set_charset('utf8mb4');

$stmt = $mysqli->prepare("CALL sp_register(?, ?, ?, @uid, @code, @msg)");
$stmt->bind_param('sss', $username, $password_hash, $email);
$username = 'php_user';
$password_hash = hash('sha256', 'secret123');
$email = 'php@example.com';
$stmt->execute();
$stmt->close();

// 獲取 OUT 參數
$result = $mysqli->query("SELECT @uid AS user_id, @code AS code, @msg AS message");
$row = $result->fetch_assoc();
echo "User ID: {$row['user_id']}, Code: {$row['code']}, Msg: {$row['message']}";

// PDO 調用
$pdo = new PDO("mysql:host=localhost;dbname=db;charset=utf8mb4", 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
]);
$stmt = $pdo->prepare("CALL sp_register(?, ?, ?, @uid, @code, @msg)");
$stmt->execute(['php_user2', hash('sha256', 'secret'), 'php2@example.com']);
$out = $pdo->query("SELECT @uid, @code, @msg")->fetch(PDO::FETCH_ASSOC);
print_r($out);
?>

進階模式：分頁游標 + 批量處理

CREATE PROCEDURE sp_batch_process_orders(
    IN p_batch_size INT DEFAULT 1000,
    IN p_status VARCHAR(20),
    OUT p_processed INT,
    OUT p_failed INT
)
BEGIN
    DECLARE v_done BOOLEAN DEFAULT FALSE;
    DECLARE v_order_id BIGINT;
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_fail INT DEFAULT 0;
    
    DECLARE cur CURSOR FOR
        SELECT id FROM orders 
        WHERE status = p_status AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
        ORDER BY id LIMIT p_batch_size;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
    
    SET p_processed = 0; SET p_failed = 0;
    
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_order_id;
        IF v_done THEN LEAVE read_loop; END IF;
        
        BEGIN
            DECLARE EXIT HANDLER FOR SQLEXCEPTION
            BEGIN
                SET v_fail = v_fail + 1;
                INSERT INTO process_log (order_id, status, error_msg, created_at)
                VALUES (v_order_id, 'FAILED', 'Processing error', NOW());
            END;
            
            -- 業務處理邏輯
            UPDATE orders SET status = 'PROCESSED', processed_at = NOW() WHERE id = v_order_id;
            INSERT INTO archive_orders SELECT * FROM orders WHERE id = v_order_id;
            DELETE FROM orders WHERE id = v_order_id;
            
            SET v_count = v_count + 1;
        END;
    END LOOP;
    CLOSE cur;
    
    SET p_processed = v_count;
    SET p_failed = v_fail;
END //

調試與性能分析

-- 查看過程定義
SHOW CREATE PROCEDURE sp_register;

-- 查看過程狀態
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 執行計劃分析 (8.0+)
EXPLAIN ANALYZE CALL sp_batch_process_orders(1000, 'PENDING', @p, @f);

-- 性能 Schema 監控
SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%sp_batch_process_orders%' ORDER BY AVG_TIMER_WAIT DESC LIMIT 5;

避坑指南

坑點	後果	解決
忘記 DELIMITER	創建失敗，語法報錯	創建前 `DELIMITER //`，結束 `DELIMITER ;`
游標未關閉	連接洩漏，報 "Commands out of sync"	必須在 EXIT HANDLER 和正常流程都 CLOSE
OUT 參數未初始化	返回 NULL 導致應用邏輯錯誤	BEGIN 即初始化所有 OUT 參數
動態 SQL 注入	安全漏洞	白名單 + 參數綁定 + quote_identifier
大事務長鎖表	阻塞所有寫入	拆分批次、提交頻率、索引優化
遞歸調用深度過大	棧溢出	改迭代、或增大 max_sp_recursion_depth (默認 0 禁用)

版本差異提示 (MySQL 8.0 新特性)

CTE (WITH) 在過程中可用，簡化複雜查詢
窗口函數 可用於過程內統計
INVISIBLE INDEX 測試索引影響
SET PERSIST 運行時持久化配置
Data Dictionary 替代 frm 文件，DDL 更快

上一篇程序編程： MySQL "Server has gone away" 徹底排查與解決方案
下一篇程序編程： PHP mysql_query 返回值詳解與遷移指南 (mysqli/PDO)

萬盛學電腦網

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

MySQL 存儲過程開發實戰：從入門到進階 (含 PHP 調用)

MySQL 存儲過程核心優勢

基礎語法速查

控制流語句

變量與游標

動態 SQL (PREPARE/EXECUTE)

⚠️ 動態 SQL 安全警示

事務控制

錯誤處理機制

PHP 調用最佳實踐 (mysqli / PDO)

進階模式：分頁游標 + 批量處理

調試與性能分析

避坑指南

版本差異提示 (MySQL 8.0 新特性)

MySQL 教學排行

推薦文章

萬盛學電腦網| 日本語版 | 設為首頁 | 加入收藏

萬盛學電腦網

您现在的位置： 萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學

MySQL 存儲過程開發實戰：從入門到進階 (含 PHP 調用)

MySQL 存儲過程核心優勢

基礎語法速查

控制流語句

變量與游標

動態 SQL (PREPARE/EXECUTE)

⚠️ 動態 SQL 安全警示

事務控制

錯誤處理機制

PHP 調用最佳實踐 (mysqli / PDO)

進階模式：分頁游標 + 批量處理

調試與性能分析

避坑指南

版本差異提示 (MySQL 8.0 新特性)

MySQL 教學排行

推薦文章

萬盛學電腦網| 日本語版 | 設為首頁 | 加入收藏

您现在的位置：萬盛學電腦網 >> 程式設計 > >> 資料庫 >> MySQL 教學