MySQL 函式

條件函式與數學函式

---

簡介

在日常的資料庫開發與維運工作中，條件函式與數學函式是最常被使用、也是最能直接提升查詢效率的工具。
條件函式讓我們可以在單一 SQL 陳述式內完成資料的判斷與轉換，省去額外的程式邏輯；而數學函式則提供了各種統計、四捨五入、取餘等運算，讓資料分析變得更直接、可讀性更高。

掌握這兩類函式，能讓你：

1. 減少資料搬移：把資料處理留在 MySQL 端，降低應用層的負擔。
2. 提升查詢效能：避免不必要的子查詢或臨時表。
3. 增進可讀性：使用語意清晰的函式，讓 SQL 更易於維護。

本文將系統性介紹 MySQL 常見的條件函式與數學函式，並提供實作範例、常見陷阱與最佳實踐，幫助你在真實專案中快速上手。

---

核心概念

1. 條件函式

1.1 IF(expr, true_val, false_val)

最簡單的三元運算子，根據 expr 的真假回傳不同值。

-- 判斷會員等級，若分數 >= 80 為「高等級」，否則「普通」
SELECT member_id,
       score,
       IF(score >= 80, '高等級', '普通') AS level
FROM members;

1.2 CASE WHEN ... THEN ... END

功能更完整，可處理多條件與範圍判斷。

SELECT order_id,
       amount,
       CASE
           WHEN amount >= 1000 THEN '大額訂單'
           WHEN amount >= 500  THEN '中額訂單'
           ELSE '小額訂單'
       END AS order_type
FROM orders;

1.3 COALESCE(expr1, expr2, ...)

回傳第一個非 NULL 的參數，常用於預設值。

-- 若電話欄位為 NULL，使用備用電話；若仍為 NULL，填入「未提供」
SELECT customer_id,
       COALESCE(phone, backup_phone, '未提供') AS contact_phone
FROM customers;

1.4 NULLIF(expr1, expr2)

若兩個表達式相等則回傳 NULL，否則回傳 expr1。常配合 IFNULL 或 COALESCE 使用。

-- 計算折扣率，若原價與折扣價相同則視為無折扣 (NULL)
SELECT product_id,
       price,
       discount_price,
       NULLIF(price, discount_price) AS diff_price
FROM products;

1.5 IFNULL(expr, alt_value)

等價於 COALESCE(expr, alt_value)，語意較簡潔。

SELECT employee_id,
       IFNULL(commission, 0) AS commission
FROM employees;

---

2. 數學函式

2.1 絕對值與符號

函式	用途	範例
ABS(x)	取得絕對值	SELECT ABS(-12) AS result; → 12
SIGN(x)	取得數字的正負號 (-1, 0, 1)	SELECT SIGN(-5) AS sign; → -1

2.2 四捨五入與取整

SELECT
    ROUND(123.4567, 2) AS round_2,   -- 123.46
    CEIL(123.001)    AS ceil_val,   -- 124
    FLOOR(123.999)   AS floor_val;  -- 123

• ROUND(x, d)：四捨五入到第 d 位小數，d 為負數時會向左取整。
• CEIL(x) / FLOOR(x)：分別返回大於/小於或等於 x 的最小/最大整數。

2.3 取餘與除法

SELECT MOD(27, 5) AS remainder;   -- 2
-- MySQL 也支援 % 運算子
SELECT 27 % 5 AS remainder2;      -- 2

2.4 次方與根號

SELECT POWER(2, 8) AS pow_val,   -- 256
       SQRT(64)   AS sqrt_val;   -- 8

2.5 指數與對數

SELECT EXP(1)      AS e,          -- 2.718281828...
       LOG(100)    AS ln_100,    -- 自然對數
       LOG10(1000) AS log10_1000;-- 常用對數

2.6 其他常用函式

函式	說明
SIN(x), COS(x), TAN(x)	三角函式，參數為弧度
RADIANS(deg)	度 → 弧度
DEGREES(rad)	弧度 → 度
TRUNCATE(x, d)	截斷到第 d 位小數，不四捨五入

---

程式碼範例（綜合應用）

以下示範一個 銷售報表，結合條件與數學函式，計算每筆訂單的「淨收入」與「折扣等級」：

SELECT
    o.order_id,
    o.amount,
    o.discount_rate,
    -- 計算折扣金額，若 discount_rate 為 NULL 則視為 0
    IFNULL(o.amount * IFNULL(o.discount_rate, 0) / 100, 0) AS discount_amount,
    -- 淨收入 = amount - discount_amount，四捨五入到小數點第二位
    ROUND(o.amount - IFNULL(o.amount * IFNULL(o.discount_rate, 0) / 100, 0), 2) AS net_income,
    -- 折扣等級：使用 CASE 判斷折扣率區間
    CASE
        WHEN IFNULL(o.discount_rate, 0) >= 30 THEN '高折扣'
        WHEN IFNULL(o.discount_rate, 0) >= 15 THEN '中折扣'
        WHEN IFNULL(o.discount_rate, 0) > 0  THEN '低折扣'
        ELSE '無折扣'
    END AS discount_level,
    -- 判斷是否為「大單」：金額 >= 1000 且淨收入 >= 800
    IF(o.amount >= 1000 AND (o.amount - IFNULL(o.amount * IFNULL(o.discount_rate, 0) / 100, 0)) >= 800,
       '大單', '普通單') AS order_category
FROM orders AS o
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31';

說明

1. IFNULL 確保即使 discount_rate 為 NULL 也能安全運算。
2. ROUND 把計算結果限制到兩位小數，避免浮點誤差。
3. CASE 與 IF 結合，提供可讀性高的分類結果。

---

常見陷阱與最佳實踐

陷阱	可能的結果	解決方式
NULL 參與運算	NULL + 5 → NULL，導致整筆資料消失	使用 IFNULL、COALESCE 先行處理預設值
資料型別不匹配	ROUND('123.45') 仍可執行，但隱式轉換可能產生警告	明確使用 CAST(col AS DECIMAL(10,2))
過度使用 CASE 造成可讀性下降	長度超過 10 條分支的 CASE 讓維護困難	考慮將邏輯抽離至視圖或儲存過程
浮點誤差	0.1 + 0.2 = 0.30000000000000004	使用 DECIMAL 型別或 ROUND/TRUNCATE 以控制精度
不必要的函式套用	在大量資料上使用 ABS(col) 但該欄位永不為負	先確認業務需求，再決定是否加入函式，減少 CPU 負擔

最佳實踐

1. 先處理 NULL：在任何算術運算前，先用 IFNULL 或 COALESCE 設定合理的預設值。
2. 盡量使用 DECIMAL：金額、稅率等需要精確計算的欄位，避免使用 FLOAT/DOUBLE。
3. 函式與索引：對索引欄位使用函式（如 ABS(col)）會導致索引失效，若必須使用，考慮建立 函式索引（MySQL 8.0+ 支援）。
4. 保持簡潔：單一 SELECT 內的條件與計算盡量保持在 3~4 層，以免閱讀困難。
5. 測試邊界值：特別是除法、取餘、對數等，確保不會因 0 或負數導致錯誤。

---

實際應用場景

1. 電商平台的促銷計算

   • 使用 IF、CASE 判斷不同優惠類型（滿額折扣、滿件免運）。
   • 結合 ROUND、CEIL 計算最終價格，確保金額四捨五入到分。

2. 金融系統的風險評估

   • ABS、POWER、SQRT 用於計算波動率、VaR（Value at Risk）。
   • COALESCE 處理缺失的市場資料，避免模型中斷。

3. IoT 數據的即時分析

   • FLOOR(timestamp/60)*60 把秒級時間戳記聚合到分鐘。
   • MOD(sensor_id, 2) 區分奇偶感測器，快速分組。

4. 報表系統的分層統計

   • GREATEST、LEAST 直接取得同一筆資料的最高/最低指標。
   • LOG10、EXP 用於指數成長與對數縮放的圖表資料。

---

總結

條件函式與數學函式是 MySQL 中不可或缺的「資料處理引擎」。透過 IF、CASE、COALESCE 等條件函式，我們可以在單一查詢內完成複雜的判斷與預設值設定；而 ROUND、ABS、POWER、SQRT 等數學函式則提供了精確且高效的計算能力。

在實務開發時，先處理好 NULL、選擇正確的資料型別、避免在索引欄位上直接套用函式，是確保效能與正確性的關鍵。掌握本文的概念與範例，你將能夠：

• 快速完成資料清理與轉換，減少程式碼量。
• 在查詢層面完成統計與分析，提升整體系統效能。
• 寫出易於維護、可讀性高的 SQL，降低團隊溝通成本。

希望這篇文章能成為你在 MySQL 開發旅程中的實用指南，祝你寫出更乾淨、更高效的查詢！ 🚀