WHERE 子句決定「哪些行參與後續處理」,是查詢性能、業務正確性的第一道關卡。本文系統整理比較/邏輯/集合/模糊/空值運算符、複合條件優先級、子查詢模式、SARGable 寫法原則。
| 類別 | 運算符 | 示例 | 說明 |
|---|---|---|---|
| 比較 | = | age = 18 | 等於 |
!= / <> | status != 'deleted' | 不等於 | |
>, >=, <, <= | score >= 60 | 範圍 | |
BETWEEN ... AND ... | age BETWEEN 18 AND 60 | 閉區間 | |
IN (val1, val2, ...) | dept IN ('IT','HR') | 離散集合 | |
IS NULL / IS NOT NULL | deleted_at IS NULL | 空值判斷 | |
| 邏輯 | AND | a=1 AND b=2 | 且(優先級高) |
OR | a=1 OR b=2 | 或 | |
NOT | NOT (a=1) | 非 | |
XOR | a=1 XOR b=2 | 異或(罕用) | |
| 模糊 | LIKE 'prefix%' | name LIKE 'Zhang%' | 前綴可用索引 |
LIKE '%suffix' | email LIKE '%@qq.com' | 後綴/中間 無法用索引 | |
LIKE '_ingle' | name LIKE '_ingle' | 單字符通配 | |
| 正則 | REGEXP 'pattern' | phone REGEXP '^1[3-9]\\d{9}$' | 正則表達式(MySQL 8.0 用 ICU) |
-- 優先級:NOT > AND > OR
WHERE a = 1 OR b = 2 AND c = 3 -- 等價 a=1 OR (b=2 AND c=3)
-- 強烈建議顯式加括號
WHERE (a = 1 OR b = 2) AND c = 3
SELECT employee_id FROM employee_stats WHERE position = 'Manager';
SELECT employee_id, salary
FROM employee_stats
WHERE position = 'Manager' AND salary > 1000;
SELECT e.address, e.last_name
FROM employee_stats s
JOIN employee_address e ON e.employee_id = s.employee_id
WHERE s.position = 'Staff';
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE vip_level > 3);
| ❌ 非 SARGable | ✅ SARGable | 原因 |
|---|---|---|
WHERE YEAR(created_at) = 2023 | WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01' | 函數破壞索引 |
WHERE col + 1 = 10 | WHERE col = 9 | 運算破壞索引 |
WHERE name LIKE '%abc%' | WHERE name LIKE 'abc%' | 前導通配符無法用索引 |
WHERE col IS NULL | WHERE col IS NULL | 可用索引(MySQL 支援) |
WHERE col != 'x' | 避免或用 NOT IN | 負向條件難用索引 |
EXPLAIN 觀察 type=ref/range、rows 小、Using indexWHERE id > last_id LIMIT 20 替代 OFFSETOR 改 UNION ALL:WHERE a=1 OR b=2 → SELECT ... WHERE a=1 UNION ALL SELECT ... WHERE b=2ANALYZE TABLE 更新統計信息,優化器才能做對選擇WHERE 是性能分水嶺:
運算符選對、括號加全、函數不包列、索引覆蓋全。
EXPLAIN 常看,SARGable 常練,慢查詢自然少。