MySQL 進階比較運算子與函數實戰：BETWEEN、GREATEST、REGEXP 等 8 大隱藏功能

前言

MySQL 提供超過 20 種比較運算子與函數，除了常用的 =、LIKE、IN 外，還有許多「冷門但強大」的工具能簡化查詢邏輯、提升效能。本文精選 8 個實用函數與運算子，附完整範例說明。

1. BETWEEN — 區間篩選利器

檢測值是否落在閉區間 [min, max] 內，支援數值、日期、字串。

SELECT 2350 BETWEEN 100 AND 10000;        -- 回傳 1 (真)
SELECT '2006-04-05' BETWEEN '2006-01-01' AND '2007-01-01'; -- 1
SELECT '2006-04-05' BETWEEN '2006-01-01' AND '2006-04-01'; -- 0

應用場景：薪資區間查詢、日期範圍報表、分頁邏輯簡化。

2. GREATEST / LEAST — 多值極值比較

從參數列表回傳最大/最小值，支援數字、日期、字串。

SELECT GREATEST('2000-06-01', '2000-05-29', '2000-06-04'); -- 2000-06-04
SELECT LEAST(100, 200, 50, -6, -73, 1000);               -- -73

優勢：避免撰寫冗長的 CASE WHEN 或多重 IF 判斷。

3. IN — 清單成員資格測試

檢查值是否存在於指定集合中，語意清晰且效能佳 (內部最佳化為常數查找)。

SELECT 'c' IN ('a', 'b', 'c', 'd'); -- 1
SELECT 1 IN (3, 4, 5);              -- 0

進階用法：搭配子查詢 WHERE col IN (SELECT id FROM ...)。

4. COALESCE — 首個非 NULL 值取得

依序評估參數，回傳第一個非 NULL 值；全為 NULL 則回傳 NULL。常用於資料清洗、預設值處理。

SELECT COALESCE(NULL, 'bat', 12, NULL); -- 'bat'
SELECT COALESCE(nickname, username, 'Guest') AS display_name FROM users;

5. INTERVAL — 數值區間索引定位

給定遞增數列 N0, N1, N2...，回傳 N0 所在區間的索引 (從 0 起算)。注意：參數列表必須已排序。

SELECT INTERVAL(24, 5, 10, 15, 20, 25, 30); -- 回傳 4
-- 邏輯：24 < 25 (索引 4) 且 > 20 (索引 3) → 區間索引 = 4

應用：評分分級、年齡分層、金額區間統計。

6. STRCMP — 字串三態比較

回傳 0 (相等)、1 (第一參數大)、-1 (第一參數小)。比較規則依當前字符集排序規則。

SELECT STRCMP('hell', 'hell');  -- 0
SELECT STRCMP('bell', 'hell');  -- -1 ('b' < 'h')
SELECT STRCMP('zell', 'hell');  -- 1

7. REGEXP — 正則表達式模式比對

功能遠超 LIKE，支援錨點、字元類別、量詞等完整 PCRE 子集。回傳 1/0。

SELECT 'The Matrix' REGEXP '[u-z]$';      -- 1 (以 u-z 結尾)
SELECT 'user123' REGEXP '^user[0-9]+$';   -- 1
SELECT 'email@test.com' REGEXP '^[^@]+@[^@]+\\.[^@]+$'; -- 1

8. DATEDIFF — 日期差天數計算

回傳 date1 - date2 的天數差 (可為負)。常搭配 NOW() 計算年齡、倒數天數。

SELECT DATEDIFF('2006-01-01', '2005-12-01'); -- 31
SELECT DATEDIFF(NOW(), '2006-02-03');        -- 截至今天的天數

注意：MySQL 8.0+ 引入 TIMESTAMPDIFF() 提供更精確單位控制 (秒、分、月等)。

總結對照表

善用這些工具，能讓 SQL 更簡潔、語意更清晰、效能更穩定。建議收藏備查！