| A | B | A AND B | A OR B |
|---|---|---|---|
| TRUE | TRUE | TRUE | TRUE |
| TRUE | FALSE | FALSE | TRUE |
| FALSE | TRUE | FALSE | TRUE |
| FALSE | FALSE | FALSE | FALSE |
| NULL | TRUE | NULL | TRUE |
| NULL | FALSE | FALSE | NULL |
| NULL | NULL | NULL | NULL |
三值邏輯:TRUE、FALSE、NULL(UNKNOWN)。NULL AND FALSE = FALSE(短路),NULL OR TRUE = TRUE(短路)。
AND 優先級高於 OR。
-- 等價於
WHERE a = 1 OR b = 2 AND c = 3
-- 解析為
WHERE a = 1 OR (b = 2 AND c = 3)
-- 明確意圖請加括號
WHERE (a = 1 OR b = 2) AND c = 3
AND:左側 FALSE/NULL → 直接返回,不評估右側OR:左側 TRUE → 直接返回,不評估右側WHERE col IS NOT NULL AND col > 0(避免 NULL 比較)WHERE status = 1 AND create_time > '2024-01-01'
-- 復合索引 (status, create_time) 完美匹配
-- 兩個單列索引:優化器選擇選擇性更高的,或 Index Merge (intersection)
WHERE status = 1 OR create_time > '2024-01-01'
-- MySQL 5.6-:幾乎總是全表掃描
-- MySQL 5.7+:Index Merge (union) 可能,但效率低
-- 8.0:更多優化,但仍不如 UNION ALL
-- 原(慢)
SELECT * FROM orders WHERE user_id = 1001 OR seller_id = 2002;
-- 優化(快,走各自索引)
SELECT * FROM orders WHERE user_id = 1001
UNION ALL
SELECT * FROM orders WHERE seller_id = 2002 AND user_id != 1001; -- 去重邏輯
-- 需求:狀態=活躍 且 (VIP 或 近30天有訂單)
SELECT * FROM users
WHERE `status` = 'active'
AND (`vip_level` > 0 OR `last_order_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY));
-- 優化:拆分 UNION ALL
SELECT * FROM users WHERE `status` = 'active' AND `vip_level` > 0
UNION
SELECT * FROM users
WHERE `status` = 'active'
AND `vip_level` = 0
AND `last_order_at` >= DATE_SUB(NOW(), INTERVAL 30 DAY);
function buildWhere(array $filters): array {
$conditions = [];
$params = [];
if (!empty($filters['name'])) {
$conditions[] = 'name LIKE ?';
$params[] = "%{$filters['name']}%";
}
if (!empty($filters['status'])) {
$conditions[] = 'status = ?';
$params[] = $filters['status'];
}
// 多條件預設 AND 組合
$where = $conditions ? 'WHERE ' . implode(' AND ', $conditions) : '';
return [$where, $params];
}
| 錯誤寫法 | 問題 | 修正 |
|---|---|---|
| WHERE status = 1 OR 2 | 解析為 (status=1) OR (2),2 為 TRUE → 全表返回 | WHERE status = 1 OR status = 2 |
| WHERE col = 'a' OR 'b' | 同上,'b' 非空字符串為 TRUE | WHERE col IN ('a','b') |
| WHERE NOT (a=1 AND b=2) | 德摩根律:等價於 a!=1 OR b!=2 | 改寫為 UNION ALL 或接受全掃 |
IS NOT NULL AND ... 防空指針IN,子查詢慎用