MySQL 儲存函數（自訂函數）建立與使用完整教學

儲存函數 vs 儲存程序

建立語法

CREATE FUNCTION function_name (param1 TYPE, param2 TYPE, ...)
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | CONTAINS SQL | NO SQL]
BEGIN
    -- 函數體
    RETURN return_value;
END;

範例：統計新聞點擊數等級

DELIMITER //

CREATE FUNCTION count_news_level(hits INT)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE lvl INT;
    IF hits < 100 THEN SET lvl = 1;
    ELSEIF hits < 1000 THEN SET lvl = 2;
    ELSEIF hits < 10000 THEN SET lvl = 3;
    ELSE SET lvl = 4;
    END IF;
    RETURN lvl;
END //

DELIMITER ;

-- 使用
SELECT title, count_news_level(hits) AS level FROM news;

函數體限制（受限語句）

以下語句**不可**在函數中使用：

ALTER, CACHE INDEX, CALL, COMMIT, CREATE, DELETE, DROP, 
FLUSH PRIVILEGES, GRANT, INSERT, KILL, LOCK, OPTIMIZE, 
REPAIR, REPLACE, REVOKE, ROLLBACK, SAVEPOINT, 
SELECT ... FROM table, SET system variable, SET TRANSACTION, 
SHOW, START TRANSACTION, TRUNCATE, UPDATE

允許：流程控制、變數宣告、條件/異常處理、不修改資料的 SELECT（僅查詢變數/常數）。

複雜範例：跨表彙總（需用儲存程序或應用層完成）

函數無法直接執行 SELECT ... FROM table 進行表查詢。若需跨表彙總（如統計每人上機次數與管理員），建議：

1. 改用儲存程序 + 游標/臨時表
2. 或在應用層（PHP/Java）多次查詢組裝
3. 或建立視圖 簡化查詢

權限與管理

-- 檢視函數
SHOW FUNCTION STATUS WHERE Db = 'db_name';
SHOW CREATE FUNCTION func_name;

-- 刪除
DROP FUNCTION IF EXISTS func_name;

-- 授權
GRANT EXECUTE ON db_name.* TO 'user'@'host';

MySQL 8.0+ 支援 CREATE OR REPLACE FUNCTION 簡化部署。函數適合封裝純計算邏輯，複雜業務流程請用儲存程序或應用層。