WHERE 子句核心語法

SELECT column_list
FROM table_name
WHERE condition
[ORDER BY ...]
[LIMIT ...];

比較運算子完整對照

運算子	說明	範例	結果
=	等於	5 = 5	TRUE
<>, !=	不等於	5 != 3	TRUE
<	小於	5 < 10	TRUE
<=	小於等於	5 <= 5	TRUE
>	大於	10 > 5	TRUE
>=	大於等於	10 >= 10	TRUE
<=>	安全等於 (NULL 安全)	NULL <=> NULL	TRUE
IS NULL	判斷 NULL	col IS NULL	TRUE/FALSE
IS NOT NULL	判斷非 NULL	col IS NOT NULL	TRUE/FALSE
BETWEEN	範圍包含端點	5 BETWEEN 1 AND 10	TRUE
IN	在列表中	5 IN (1,3,5)	TRUE
NOT IN	不在列表中	5 NOT IN (1,3)	TRUE
LIKE	模式匹配	'abc' LIKE 'a%'	TRUE
REGEXP	正則表達式	'abc' REGEXP '^a'	TRUE

邏輯運算子與優先級

-- 優先級：NOT > AND > OR
WHERE a = 1 AND b = 2 OR c = 3   -- 等同 (a=1 AND b=2) OR c=3
WHERE a = 1 AND (b = 2 OR c = 3) -- 建議明確加括號

模式匹配：LIKE 與 REGEXP

LIKE 萬用字元

符號	含義	範例匹配
%	任意長度字串 (含 0 字元)	'abc' LIKE 'a%' ✓
_	單一字元	'abc' LIKE 'a_c' ✓
\%	字面 % (需轉義)	'100%' LIKE '100\%%' ESCAPE '\' ✓
\_	字面 _ (需轉義)	'a_b' LIKE 'a\_b' ESCAPE '\' ✓

LIKE 索引使用規則

• LIKE 'prefix%'：可使用索引 (最左前綴)
• LIKE '%suffix'：全表掃描
• LIKE '%middle%'：全表掃描
• 解決方案：全文索引 (FULLTEXT)、外部搜尋引擎 (Elasticsearch)

REGEXP 正則表達式 (MySQL 8.0 使用 ICU 引擎)

-- 基本用法
SELECT * FROM users WHERE username REGEXP '^j.*n$';  -- 以 j 開頭 n 結尾

-- 常用模式
'^[0-9]+$'           -- 純數字
'^[a-zA-Z0-9_]+$'    -- 字母數字底線
'^1[3-9]\d{9}$'      -- 中國大陸手機號
'^.+@.+\..+$'        -- 簡易 Email 格式

NULL 值特殊處理

-- ❌ 錯誤：NULL 不能用 = 比較
WHERE col = NULL        -- 永遠為 UNKNOWN
WHERE col != NULL       -- 永遠為 UNKNOWN

-- ✅ 正確
WHERE col IS NULL
WHERE col IS NOT NULL
WHERE col <=> NULL      -- NULL 安全等於 (等同 IS NULL)

-- 函數處理
IFNULL(col, 'default')     -- MySQL 專用
COALESCE(col1, col2, 'd')  -- 標準 SQL，回傳第一個非 NULL
NULLIF(col, 'value')       -- 相等則回傳 NULL

實戰範例：會員查詢

-- 1. 精確匹配
SELECT * FROM members WHERE username = 'john';

-- 2. 多條件 AND
SELECT * FROM members WHERE status = 1 AND created_at >= '2024-01-01';

-- 3. 多條件 OR (注意括號)
SELECT * FROM members WHERE (status = 1 OR status = 2) AND level > 5;

-- 4. 範圍查詢
SELECT * FROM orders WHERE amount BETWEEN 100 AND 1000;

-- 5. 列表匹配
SELECT * FROM products WHERE category_id IN (1, 3, 5, 8);

-- 6. 模糊搜尋 (姓名包含關鍵字)
SELECT * FROM members WHERE username LIKE CONCAT('%', ?, '%');

-- 7. 空值處理
SELECT * FROM members WHERE nickname IS NULL OR nickname = '';

-- 8. 子查詢條件
SELECT * FROM orders WHERE user_id IN (SELECT id FROM members WHERE level > 10);

效能優化建議

1. WHERE 條件順序：優先放高選擇性 (區分度高) 的條件，或符合複合索引最左前綴的欄位
2. 避免隱含轉型：WHERE varchar_col = 123 會導致索引失效，用 WHERE varchar_col = '123'
3. IN 列表過長：超過 1000 個值考慮用臨時表 JOIN 或 EXISTS
4. OR 條件優化：改寫為 UNION ALL 讓各分支走各自索引
5. 使用覆蓋索引：SELECT id, name FROM t WHERE status=1 配合索引 (status, id, name) 避免回表

PHP 預處理語句範例

$stmt = $mysqli->prepare("
    SELECT id, username, email 
    FROM members 
    WHERE status = ? AND created_at >= ? 
    ORDER BY id DESC LIMIT ?
");
$stmt->bind_param('isi', $status, $date, $limit);
$status = 1;
$date = '2024-01-01';
$limit = 20;
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) { ... }