函數 vs 預存程序：關鍵差異

特性	FUNCTION	PROCEDURE
返回值	必須 透過 RETURNS 指定型別，且 必須 有 RETURN 陳述式	無返回值（可用 OUT 參數）
參數模式	僅支援 IN（預設）	支援 IN、OUT、INOUT
呼叫方式	SELECT func(...) 在 SQL 表達式中	CALL proc(...) 獨立陳述式
事務控制	❌ 不可含 COMMIT/ROLLBACK	✅ 可管理事務
修改資料	❌ 不可執行 DML（INSERT/UPDATE/DELETE）	✅ 可執行 DML

CREATE FUNCTION 完整語法

CREATE [DEFINER = user] FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...]
routine_body

func_parameter:
    param_name type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT 'string'

實戰範例：智慧型姓名格式化函數

需求：依性別自動加稱謂（Mr/Ms），支援中間名可選。

DELIMITER $$
CREATE FUNCTION format_name(
    in_title VARCHAR(4),      -- 傳入稱謂，NULL 表示自動判斷
    in_gender CHAR(1),        -- 'M' 或 'F'
    in_firstname VARCHAR(20),
    in_middle_initial CHAR(1), -- 中間名縮寫，可 NULL
    in_surname VARCHAR(20)
) RETURNS VARCHAR(60)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE v_title VARCHAR(4);
    DECLARE v_full_name VARCHAR(60);

    -- 1. 自動判斷稱謂
    IF in_title IS NULL THEN
        SET v_title = CASE in_gender
            WHEN 'M' THEN 'Mr'
            WHEN 'F' THEN 'Ms'
            ELSE 'Mx'
        END;
    ELSE
        SET v_title = in_title;
    END IF;

    -- 2. 組合全名
    IF in_middle_initial IS NULL THEN
        SET v_full_name = CONCAT(v_title, ' ', in_firstname, ' ', in_surname);
    ELSE
        SET v_full_name = CONCAT(v_title, ' ', in_firstname, ' ', in_middle_initial, ' ', in_surname);
    END IF;

    RETURN v_full_name;
END$$
DELIMITER ;

呼叫與測試

-- 自動判斷稱謂
SELECT format_name(NULL, 'M', 'John', 'Q', 'Public');
-- Mr John Q Public

SELECT format_name(NULL, 'F', 'Jane', NULL, 'Doe');
-- Ms Jane Doe

-- 明確指定稱謂
SELECT format_name('Dr', 'M', 'Henry', 'J', 'Smith');
-- Dr Henry J Smith

進階特性詳解

1. DETERMINISTIC / NOT DETERMINISTIC

• DETERMINISTIC：相同輸入必定產生相同輸出，優化器可快取結果
• 預設 NOT DETERMINISTIC，若函數呼叫 RAND()、NOW()、查詢資料表則必須聲明為 NOT DETERMINISTIC

2. SQL 資料存取特性

特性	說明
CONTAINS SQL	不讀寫資料（預設）
NO SQL	不包含 SQL 陳述式
READS SQL DATA	包含 SELECT（如範例）
MODIFIES SQL DATA	包含 DML，**函數禁止使用**

3. SQL SECURITY

• DEFINER（預設）：以建立者權限執行
• INVOKER：以呼叫者權限執行，需呼叫者有底層表權限

版本相容性與已知問題

早期版本（MySQL 4.1.x / 5.0.x）存在函數快取、字元集轉換等 Bug，導致 RETURN NULL 或編碼異常。MySQL 5.1.24 / 5.0.67 以上已修復。現代版本（5.7、8.0）穩定可用。

最佳實務清單

• [ ] 函數命名避開保留字與內建函數（如 trim、date），必要時加前綴 fn_
• [ ] 明確宣告 DETERMINISTIC / READS SQL DATA，協助優化器
• [ ] 輸入參數加 IN 關鍵字提升可讀性
• [ ] 所有路徑都要有 RETURN，避免隱性 NULL
• [ ] 複雜邏輯拆分為多個小函數，便於測試與複用
• [ ] 以 SELECT func(...) 在 WHERE、ORDER BY、SELECT 列表中直接使用

刪除與查看函數

DROP FUNCTION IF EXISTS format_name;
SHOW CREATE FUNCTION format_name;
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_NAME='format_name';

善用自訂函數封裝商業邏輯，可讓 SQL 陳述式更簡潔、可維護性更高，但請記住：函數是純計算單元，不可有副作用。