MySQL 函數詳解：內置函數分類與自定義函數（UDF）創建實戰

函數 vs 存儲過程

常用內置函數速查

數學

ABS(-5)        -- 5
CEIL(3.14)     -- 4
FLOOR(3.99)    -- 3
ROUND(3.14159, 2) -- 3.14
POW(2, 10)     -- 1024
RAND()         -- 0~1 隨機數

字符串

CONCAT('a','b','c')      -- 'abc'
SUBSTRING('hello', 2, 3) -- 'ell'
TRIM('  hi  ')           -- 'hi'
REPLACE('a_b_c', '_', '-') -- 'a-b-c'
LENGTH('中文')           -- 6 (字節)
CHAR_LENGTH('中文')      -- 2 (字符)

日期時間

NOW()                    -- 當前 datetime
CURDATE()                -- 當前 date
CURTIME()                -- 當前 time
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
DATEDIFF('2024-12-31', '2024-01-01') -- 365
DATE_ADD(NOW(), INTERVAL 1 DAY)

流程控制

IF(cond, true_val, false_val)
CASE WHEN cond1 THEN v1 WHEN cond2 THEN v2 ELSE v3 END
COALESCE(NULL, NULL, 'first_non_null') -- 'first_non_null'
NULLIF('same', 'same')   -- NULL

自定義函數 (UDF) 創建實戰

示例：判斷數值是否 ≥ 10

DELIMITER ;;
CREATE FUNCTION func_compare (a INT) RETURNS VARCHAR(200) CHARSET utf8
DETERMINISTIC  -- 相同輸入必產生相同輸出，優化器可緩存
BEGIN
  IF a >= 10 THEN
    RETURN '大於等於10';
  ELSE
    RETURN '小於10';
  END IF;
END;;
DELIMITER ;

-- 驗證
SELECT func_compare(9), func_compare(11);
-- 結果: '小於10', '大於等於10'

關鍵字段說明

• DETERMINISTIC / NOT DETERMINISTIC：聲明確定性，**必須誠實標註**，否則查詢緩存可能返回錯誤結果
• READS SQL DATA / MODIFIES SQL DATA / NO SQL：聲明數據訪問類型
• SQL SECURITY DEFINER | INVOKER：執行權限上下文

調用場景對比

-- 函數：可直接用於 WHERE/SELECT/ORDER BY
SELECT * FROM orders WHERE func_compare(amount) = '大於等於10';

-- 存儲過程：需 CALL，不可嵌入查詢
CALL proc_order_stats(@total, @avg);

最佳實踐

1. **純計算/轉換邏輯** → 函數（可索引優化、可緩存）
2. **涉及寫操作/事務/多步驟** → 存儲過程
3. 避免在函數中執行耗時查詢（會導致調用端 SQL 極慢）
4. 命名規範：func__（如 func_util_mask_phone）
5. 單元測試：覆蓋邊界值、NULL、異常輸入

善用函數封裝通用邏輯，讓 SQL 更簡潔、更可讀、更易維護。