WHERE 子句核心語法
SELECT `column_list`
FROM `table_name`
WHERE `condition_expression`;
比較運算子完整對照
| 運算子 | 含義 | 範例 | 索引友善度 |
|---|
= | 等於 | status = 1 | ✅ 最佳 (ref/const) |
<> / != | 不等於 | status <> 0 | ⚠️ 範圍掃描 |
> | 大於 | age > 18 | ✅ 範圍查詢 |
< | 小於 | price < 100 | ✅ 範圍查詢 |
>= | 大於等於 | created_at >= '2024-01-01' | ✅ 範圍查詢 |
<= | 小於等於 | score <= 60 | ✅ 範圍查詢 |
BETWEEN ... AND ... | 範圍包含邊界 | age BETWEEN 18 AND 60 | ✅ 等價 >= AND <=' |
IN (...) | 列表匹配 | id IN (1,2,3) | ✅ 優化器轉為多個 = |
NOT IN (...) | 不在列表 | status NOT IN (0,-1) | ⚠️ 子查詢慎用 |
LIKE 'pattern' | 模糊匹配 | name LIKE '張%' | ✅ 前綴可用索引 |
LIKE '%pattern%' | 包含匹配 | content LIKE '%關鍵字%' | ❌ 全表掃描 |
IS NULL | 為空 | deleted_at IS NULL | ✅ 可用索引 |
IS NOT NULL | 不為空 | email IS NOT NULL | ✅ 可用索引 |
REGEXP | 正則表達式 | phone REGEXP '^09\\d{8}$' | ❌ 全表掃描 |
邏輯運算子與優先級
-- 優先級：NOT > AND > OR
-- 明確分組請用括號
SELECT * FROM orders
WHERE (status = 1 OR status = 2)
AND created_at >= '2024-01-01'
AND user_id NOT IN (SELECT id FROM banned_users);
實戰範例：電商訂單篩選
-- 待出貨訂單：已付款、未出貨、非取消、近 30 天
SELECT `order_id`, `user_id`, `total_amount`, `created_at`
FROM `orders`
WHERE `payment_status` = 'paid'
AND `shipping_status` = 'pending'
AND `order_status` NOT IN ('cancelled', 'refunded')
AND `created_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY `created_at` ASC
LIMIT 50;
索引優化：讓 WHERE 走索引
| WHERE 模式 | 建議索引 | EXPLAIN type | 避坑提醒 |
|---|
col = ? | INDEX idx_col (col) | ref/const | |
col > ? | INDEX idx_col (col) | range | |
col1 = ? AND col2 = ? | INDEX idx_c1_c2 (col1, col2) | ref | 最左前綴：col1 必須在前 |
col1 = ? AND col2 > ? | INDEX idx_c1_c2 (col1, col2) | ref/range | col1 等值、col2 範圍 |
col1 > ? AND col2 = ? | INDEX idx_c2_c1 (col2, col1) | ref/range | 等值欄位優先放前面 |
col LIKE 'prefix%' | INDEX idx_col (col) | range | 前綴查詢可用索引 |
col LIKE '%suffix' | 無法用索引 | ALL | 考慮全文索引或反轉欄位 |
func(col) = ? | 無法用索引 | ALL | 改用函數索引 (MySQL 8.0) 或冗余欄位 |
常見效能殺手
- 隱式轉換：
WHERE varchar_col = 123 → 全表掃描。字串比字串、數值比數值
- 函數包裹欄位：
WHERE DATE(created_at) = '2024-01-01' → 不走索引。改用 created_at >= '2024-01-01' AND created_at < '2024-01-02'
- OR 橫跨不同索引：
WHERE idx_col1 = 1 OR idx_col2 = 2 → 可能全掃。用 UNION 分開查再合併
- NOT IN 子查詢：
WHERE id NOT IN (SELECT ...) → 慢。改用 NOT EXISTS 或 LEFT JOIN ... WHERE IS NULL
- 字串前導通配符：
LIKE '%keyword%' → 全掃。用全文索引或 Elasticsearch
MySQL 8.0 新功能：函數索引與隱形索引
-- 函數索引：直接為運算式建索引
ALTER TABLE `orders` ADD INDEX `idx_date_created` ((DATE(`created_at`)));
-- 現在 WHERE DATE(created_at) = '2024-01-01' 可走索引
-- 隱形索引：測試移除影響不實際刪除
ALTER TABLE `t` ALTER INDEX `idx_name` INVISIBLE;
-- 優化器忽略此索引，驗證效能後再決定 DROP
總結
MySQL WHERE 子句是查詢效能分水嶺。核心原則：等值優於範圍、最左前綴、避免函數包裹欄位、隱式轉換、OR 橫跨索引。善用 EXPLAIN 驗證 type=ref/range 而非 ALL。複雜條件用括號明確優先級，大量文字搜尋上全文索引或專業搜尋引擎。
