MySQL 8.0 存儲過程與自定義函數深度實戰指南

MySQL 8.0 存儲過程與自定義函數深度實戰指南

MySQL 存儲過程與自定義函數是將業務邏輯下沉到數據庫層的利器，能減少網絡交互、提升執行效率、封裝復雜邏輯。本文系統講解：函數與過程的語法差異、參數模式（IN/OUT/INOUT）、復合結構體、調用方式、權限控制，並對比兩者適用場景，基於 MySQL 8.0 語法演示。

一、自定義函數 (CREATE FUNCTION)

核心要素

• 參數列表： 可為空，每個參數需指定類型，默認 IN 模式
• 返回類型： 必須通過 RETURNS 聲明，函數體必須包含 RETURN 語句
• 函數體： 簡單語句可直接寫；多語句需包裹在 BEGIN ... END 中
• 確定性聲明： 建議顯式指定 DETERMINISTIC 或 NOT DETERMINISTIC

1. 無參數函數：獲取中文格式當前時間

DELIMITER //
CREATE FUNCTION f_chinese_now()
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
    RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H點:%i分:%s秒');
END //
DELIMITER ;

-- 調用
SELECT f_chinese_now();

2. 帶參數函數：兩數求和返回浮點數

CREATE FUNCTION f_add_float(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS DECIMAL(10,2) UNSIGNED
DETERMINISTIC
RETURN num1 + num2;

SELECT f_add_float(32, 33); -- 65.00

3. 復合結構函數：插入用戶並返回自增 ID

演示 DELIMITER 臨時修改語句結束符，避免函數體內分號衝突。

DELIMITER //
CREATE FUNCTION f_add_user(p_username VARCHAR(20))
RETURNS INT UNSIGNED
NOT DETERMINISTIC
BEGIN
    INSERT INTO `test` (`username`) VALUES (p_username);
    RETURN LAST_INSERT_ID();
END //
DELIMITER ;

SELECT f_add_user('Rose'); -- 返回新記錄 ID

函數管理命令

SHOW FUNCTION STATUS WHERE Db = 'test';  -- 查看
DROP FUNCTION IF EXISTS f_add_user;      -- 刪除

二、存儲過程 (CREATE PROCEDURE)

核心差異

參數模式詳解

• IN： 輸入參數，調用時必須提供，內部修改不影響外部
• OUT： 輸出參數，調用時傳入用戶變量（@var），內部賦值後外部可讀取
• INOUT： 輸入輸出參數，調用時提供初始值，內部可修改並返回

1. 無參數過程：查看版本

CREATE PROCEDURE sp_version()
SELECT VERSION();

CALL sp_version(); -- 或 CALL sp_version

2. IN 參數：按 ID 刪除用戶

DELIMITER //
CREATE PROCEDURE sp_del_user(IN p_id INT UNSIGNED)
BEGIN
    DELETE FROM `test` WHERE `id` = p_id;
END //
DELIMITER ;

CALL sp_del_user(3);

⚠ 避坑指南： 參數名勿與表字段名重名（如 id），否則 WHERE id = id 恆真導致全表刪除。建議參數加前綴 p_。

3. IN + OUT 參數：刪除並返回剩餘記錄數

DELIMITER //
CREATE PROCEDURE sp_del_count(IN p_id INT UNSIGNED, OUT p_remain INT UNSIGNED)
BEGIN
    DELETE FROM `test` WHERE `id` = p_id;
    SELECT COUNT(*) INTO p_remain FROM `test`;
END //
DELIMITER ;

CALL sp_del_count(3, @remain);
SELECT @remain; -- 用戶變量以 @ 開頭，會話級作用域

4. 多 OUT 參數：按年齡刪除並返回刪除數與剩餘數

DELIMITER //
CREATE PROCEDURE sp_del_age(IN p_age SMALLINT UNSIGNED,
                            OUT p_deleted SMALLINT UNSIGNED,
                            OUT p_remain SMALLINT UNSIGNED)
BEGIN
    DELETE FROM `test` WHERE `age` = p_age;
    SELECT ROW_COUNT() INTO p_deleted;
    SELECT COUNT(*) INTO p_remain FROM `test`;
END //
DELIMITER ;

CALL sp_del_age(20, @del, @rem);
SELECT @del, @rem;

三、進階特性與最佳實踐

流程控制語句

存儲過程支持 IF...ELSEIF...ELSE...END IF、CASE、WHILE、REPEAT、LOOP、ITERATE、LEAVE 等，可實現複雜業務邏輯。

異常處理

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    ROLLBACK;
    SELECT 'ERROR' AS result;
END;

權限與安全

• 創建者需 CREATE ROUTINE 權限，調用者需 EXECUTE 權限
• 使用 DEFINER / INVOKER 指定執行上下文，防止權限提升
• 避免在高併發核心鏈路使用，防止數據庫 CPU 成為瓶頸

性能優化建議

1. 適當使用：僅封裝高頻、多步、事務性強的操作
2. 減少結果集返回：使用 OUT 參數替代 SELECT 返回大量數據
3. 索引配合：過程內 SQL 需有相應索引支持
4. 監控分析：定期 SHOW PROCEDURE STATUS、EXPLAIN 內部查詢