萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 特殊比較運算符實戰:BETWEEN、GREATEST/LEAST、IN/NOT IN 進階用法

BETWEEN:區間判斷的最佳寫法

-- 數值區間
SELECT 2350 BETWEEN 100 AND 10000;  -- 1 (TRUE)

-- 日期區間 (字串比較同理)
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

優勢:語意清晰、優化器易識別為範圍掃描、可用索引。

陷阱:BETWEEN 為**閉區間** [min, max],日期若含時間部分需注意邊界:

-- 錯誤:漏掉 2024-01-01 12:00:00
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- 正確:半開區間
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

GREATEST / LEAST:多值極值一行搞定

-- 取最大值
SELECT GREATEST(20000601, 20000529, 20000604); -- 20000604

-- 取最小值
SELECT LEAST(100, 200, 50, -6, -73, 1000);   -- -73

-- 實戰:計算多欄位最新更新時間
SELECT GREATEST(updated_at, profile_updated_at, password_changed_at) AS last_activity FROM users;

支援數值、日期、字串 (按字典序),參數中有 NULL 結果為 NULL。

IN / NOT IN:集合成員資格測試

-- 等值清單
SELECT * FROM orders WHERE status IN ('paid','shipped','completed');

-- 子查詢
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_members);

-- 反向
SELECT * FROM products WHERE category_id NOT IN (1,2,3);

致命陷阱:NOT IN 子查詢若包含 NULL,整個結果為空!

-- 危險範例
SELECT * FROM t1 WHERE id NOT IN (SELECT col FROM t2); -- 若 t2.col 有 NULL,回 0 行
-- 安全寫法
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t2.col = t1.id);

其他實用比較函數

函數/運算符用途範例
ISNULL(expr)判斷是否為 NULLSELECT ISNULL(NULL); -- 1
COALESCE(v1,v2,...)回傳第一個非 NULL 值COALESCE(phone, mobile, 'N/A')
NULLIF(v1,v2)相等回 NULL,否則回 v1NULLIF(score, 0) 避免除零
STRCMP(s1,s2)字串比較 -1/0/1STRCMP('a','b') = -1
INTERVAL(N, n1,n2,n3...)回傳 N 落在哪個區間索引INTERVAL(5, 1,3,7,10) = 2 (3-7 區間)

效能建議

  • BETWEEN、IN 皆可利用索引範圍掃描,優於 OR 連接多條件。
  • IN 清單過長 (>1000) 可能導致優化器選擇全表掃描,考慮臨時表或 JOIN。
  • 避免在索引欄位上使用函數/運算 (如 WHERE YEAR(date)=2024),改用範圍查詢。
copyright © 萬盛學電腦網 all rights reserved