MySQL 自定義函數開發指南：語法、權限、字元集與最佳實踐

MySQL 自定義函數開發指南：語法、權限、字元集與最佳實踐

MySQL 支援使用者自定義函數 (UDF)，可封裝業務邏輯、簡化 SQL、提升複用性。本文涵蓋建立語法、決定性聲明、字元集陷阱、權限控制、以及生產環境避坑指南。

基本語法結構

CREATE FUNCTION `function_name` (param1 TYPE, param2 TYPE, ...)
RETURNS RETURN_TYPE
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL | CONTAINS SQL]
[SQL SECURITY {DEFINER | INVOKER}]
[COMMENT 'description']
BEGIN
  -- 函數體
  DECLARE var_name TYPE DEFAULT value;
  -- 邏輯
  RETURN result_value;
END;

完整範例：狀態碼轉中文

DELIMITER $$

DROP FUNCTION IF EXISTS `fn_status_to_zh`$$

CREATE FUNCTION `fn_status_to_zh` (`p_status` VARCHAR(20))
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
SQL SECURITY INVOKER
COMMENT '將英文狀態碼轉換為中文顯示'
BEGIN
  DECLARE `v_result` VARCHAR(50) DEFAULT '未知';
  
  CASE `p_status`
    WHEN 'online'  THEN SET `v_result` = '上線';
    WHEN 'offline' THEN SET `v_result` = '下線';
    WHEN 'pending' THEN SET `v_result` = '待審';
    WHEN 'banned'  THEN SET `v_result` = '封禁';
    ELSE SET `v_result` = '未知';
  END CASE;
  
  RETURN `v_result`;
END$$

DELIMITER ;

-- 測試
SELECT `fn_status_to_zh`('online');   -- 上線
SELECT `fn_status_to_zh`('unknown');  -- 未知

關鍵字句詳解

字元集陷阱與解決

**現象**：函數參數/變數/返回值出現亂碼，特別是中文參數傳入。

**原因**：函數預設使用資料庫預設字元集，若資料庫是 latin1 而連線是 utf8mb4，參數會被錯誤轉碼。

**解決**：

-- 1. 顯式指定參數/返回值字元集
CREATE FUNCTION `fn_test` (`p_str` VARCHAR(100) CHARACTER SET utf8mb4)
RETURNS VARCHAR(200) CHARACTER SET utf8mb4
...

-- 2. 變數宣告指定字元集
DECLARE `v_str` VARCHAR(100) CHARACTER SET utf8mb4 DEFAULT '';

-- 3. 函數建立時指定整體字元集（8.0+）
CREATE FUNCTION `fn_test` (...) RETURNS ... CHARACTER SET utf8mb4 ...

權限控制

-- 授予建立函數權限
GRANT CREATE ROUTINE ON `mydb`.* TO 'dev_user'@'%';

-- 授予執行函數權限
GRANT EXECUTE ON `mydb`.* TO 'app_user'@'%';

-- 查看函數定義
SHOW CREATE FUNCTION `fn_status_to_zh`;
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'fn_status_to_zh';

二進位日誌與複製安全

啟用 Binlog (log_bin=ON) 時，建立函數需滿足下列任一條件，否則報錯 ERROR 1418 (HY000)：

1. 函數標記 DETERMINISTIC 且 READS SQL DATA 或 NO SQL
2. 全域變數 log_bin_trust_function_creators = ON

-- 線上暫時開啟（重啟失效）
SET GLOBAL log_bin_trust_function_creators = 1;

-- 永久生效 (my.cnf)
[mysqld]
log_bin_trust_function_creators = 1

效能與最佳實踐

存儲函數 vs 存儲過程 vs 視圖 選型表