MySQL 儲存函數建立、刪除、修改與查看完整指南

MySQL 儲存函數建立、刪除、修改與查看完整指南

MySQL 儲存函數（Stored Function）可封裝可重複使用的邏輯，回傳單一值，可在 SQL 語句中直接呼叫。本文涵蓋建立語法、參數模式、呼叫方式、刪除與狀態查看。

語法結構

CREATE FUNCTION function_name ([param_mode param_name param_type, ...])
RETURNS return_type
[characteristic ...]
routine_body

• param_mode：IN（預設）、OUT、INOUT（函數僅支援 IN）
• characteristic：DETERMINISTIC、NO SQL、READS SQL DATA、MODIFIES SQL DATA、SQL SECURITY {DEFINER|INVOKER}、COMMENT '...'

實戰範例

1. 建立簡單函數

DELIMITER $$
CREATE FUNCTION hello(w CHAR(20) CHARSET utf8)
RETURNS CHAR(50)
READS SQL DATA
BEGIN
  RETURN CONCAT('hello ', w, '!');
END $$
DELIMITER ;

• DELIMITER $$ 暫時變更語句結束符，避免函數體內 ; 衝突。
• READS SQL DATA 聲明函數讀取資料但不修改，符合二進制日誌安全要求。

2. 呼叫函數

SELECT hello('日記本的回憶');

結果：hello 日記本的回憶!

3. 刪除函數

DROP FUNCTION hello;

4. 查看現有函數

SHOW FUNCTION STATUS \G

或查詢 information_schema.routines：

SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, ROUTINE_DEFINITION
FROM information_schema.routines
WHERE ROUTINE_SCHEMA = 'your_db' AND ROUTINE_TYPE = 'FUNCTION';

進階：帶邏輯判斷的函數

DELIMITER $$
CREATE FUNCTION get_grade(score INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
  DECLARE grade VARCHAR(10);
  IF score >= 90 THEN SET grade = '優秀';
  ELSEIF score >= 80 THEN SET grade = '良好';
  ELSEIF score >= 60 THEN SET grade = '及格';
  ELSE SET grade = '不及格';
  END IF;
  RETURN grade;
END $$
DELIMITER ;

注意事項

• 函數不能執行 DDL/DML 修改資料（需用 MODIFIES SQL DATA 特性，但受限於二進制日誌格式，生產環境建議改用預存程序）。
• 函數回傳值必須為標量（單一值），不可回傳結果集。
• MySQL 要求建立函數時宣告 DETERMINISTIC、NO SQL 或 READS SQL DATA 其中之一，否則啟用 log_bin 時會報錯。
• 避免在高頻查詢的 WHERE 條件中呼叫複雜函數，導致無法使用索引（非 SARGable）。