萬盛學電腦網

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

SQL WHERE 子句完整指南:運算符、模式匹配、NULL 處理、SARGable 原則

語法結構

SELECT select_list
FROM table_name
WHERE search_condition
[ORDER BY ...] [LIMIT ...];

比較運算符

運算符含義示例索引友好
=等於status = 1✅ 最佳
<> / !=不等於status != 1❌ 通常全掃
>, >=, <, <=範圍比較age >= 18✅ 範圍掃描
BETWEEN ... AND ...閉區間age BETWEEN 18 AND 60✅ 等價 >= AND <=
IN (val1, val2, ...)集合成員status IN (1,2,3)✅ 常量列表走索引
NOT IN非集合成員status NOT IN (1,2)⚠️ 子查詢慎用
IS NULL / IS NOT NULL空值判斷deleted_at IS NULL⚠️ 部分引擎不走索引
LIKE 'prefix%'前綴匹配name LIKE 'Zhang%'✅ 走索引
LIKE '%suffix'後綴/包含匹配name LIKE '%son'❌ 全掃
REGEXP / RLIKE正則匹配phone REGEXP '^1[3-9]\\d{9}$'❌ 8.0 前全掃

SARGable (Search ARGument ABLE) 原則

「讓索引能被搜索參數使用」—— 將列孤立在運算符一側,避免對列進行函數/運算/類型轉換。

❌ 非 SARGable(殺索引)

WHERE YEAR(created_at) = 2024
WHERE DATE(created_at) = '2024-01-15'
WHERE LOWER(email) = 'test@ex.com'
WHERE amount + 100 > 500
WHERE SUBSTRING(phone, 1, 3) = '138'
WHERE id + 0 = 1001  -- 隱式轉換

✅ SARGable(走索引)

WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE email = 'test@ex.com'  -- 列定義同字符集/排序規則
WHERE amount > 400
WHERE phone LIKE '138%'
WHERE id = 1001

NULL 值處理陷阱

-- 錯誤:NULL = NULL 為 UNKNOWN,不為 TRUE
WHERE col = NULL          -- 永遠不匹配
WHERE col != NULL         -- 永遠不匹配

-- 正確
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE COALESCE(col, 'default') = 'value'  -- 應用層處理更好

-- NOT IN 陷阱
WHERE id NOT IN (1, 2, NULL)  -- 結果永遠為空!
-- 修正
WHERE id NOT IN (SELECT id FROM t WHERE id IS NOT NULL)
-- 或
WHERE NOT EXISTS (SELECT 1 FROM t WHERE t.id = outer.id)

模式匹配最佳實踐

動態條件構建模式

-- 應用層構建(推薦)
$where = ['1=1']; $params = [];
if ($name) { $where[] = 'name LIKE ?'; $params[] = "%$name%"; }
if ($status) { $where[] = 'status = ?'; $params[] = $status; }
$sql = "SELECT * FROM users " . implode(' AND ', $where);

-- 避免:WHERE 1=1 AND (name = '' OR 1=1) 等偽動態

多條件索引設計

-- 查詢:WHERE a=1 AND b=2 ORDER BY c
-- 最優索引:INDEX (a, b, c)  -- 等值列在前,排序列在後

-- 查詢:WHERE a=1 OR b=2
-- 無完美索引 → 改寫 UNION ALL,各建單列索引

總結檢查清單

  • [ ] WHERE 列是否有合適索引?
  • [ ] 是否對列使用函數/運算/隱式轉換?
  • [ ] OR 條件是否可改寫為 UNION ALL?
  • [ ] NULL 處理是否正確(IS NULL / COALESCE)?
  • [ ] LIKE 是否為前綴模式?
  • [ ] 參數化查詢是否避免字符串拼接?
  • [ ] EXPLAIN 確認 type=ref/range,而非 ALL/index?
copyright © 萬盛學電腦網 all rights reserved
需求推薦寫法索引備選
精確匹配=✅
前綴匹配LIKE 'abc%'✅
後綴匹配反轉列 + 前綴 / 全文索引❌生成列 + 索引
包含匹配全文索引 / Elasticsearch❌Trigram 索引 (pg_trgm)
正則表達式應用層預過濾 + REGEXP❌MySQL 8.0 功能性索引