您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL WHERE 條件中 BETWEEN 與 IN 深度解析：邊界語義、子查詢、效能與最佳實踐

編輯：MySQL 資料庫相關知識

前言

BETWEEN ... AND 與 IN 是 WHERE 條件中表達「範圍」與「集合」的兩大運算子。看似簡單，但邊界包含性、資料型別隱式轉換、子查詢效能、索引使用條件皆暗藏細節。本文系統剖析語義、實戰陷阱與優化策略。

一、BETWEEN ... AND

1.1 語法與邊界語義

WHERE col BETWEEN val1 AND val2  -- 等價於 col >= val1 AND col <= val2 (包含邊界)
WHERE col NOT BETWEEN val1 AND val2  -- 等價於 col < val1 OR col > val2

**關鍵**：MySQL **包含雙邊界** (閉區間)。不同資料庫可能不同 (如 PostgreSQL 同包含，部分舊版本排除上界)，**跨庫遷移必須驗證**。

1.2 資料型別與隱式轉換

欄位型別	邊界值寫法	注意事項
數值 (INT, DECIMAL)	`BETWEEN 1 AND 100`	直接比較，無陷阱
日期/時間 (DATE, DATETIME, TIMESTAMP)	`BETWEEN '2024-01-01' AND '2024-01-31 23:59:59'`	字串隱式轉換依賴 session `time_zone`；建議用 `>= '2024-01-01' AND < '2024-02-01'` 避免秒級邊界問題
字串 (CHAR, VARCHAR)	`BETWEEN 'a' AND 'z'`	按排序規則字典序比較；`utf8mb4_general_ci` 不區分大小寫，`BETWEEN 'A' AND 'z'` 含大小寫字母

1.3 索引使用

欄位有索引且無隱式轉換 → range 存取類型，效能佳。
BETWEEN 邊界為常數或確定參數時，優化器可精確估算範圍行數。

1.4 實戰陷阱：時間範圍查詢

-- 錯誤：漏掉 2024-01-31 當天 23:59:59 之後的資料
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

-- 正確：半開半閉區間
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'

-- 或使用 DATE()
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-01-31'  -- 但無法用索引

二、IN 操作符

2.1 語法與集合語義

WHERE col IN (val1, val2, ...)      -- 集合成員資格
WHERE col NOT IN (val1, val2, ...)    -- 非成員 (注意 NULL 陷阱)

2.2 IN 子查詢

-- 找出狀態正常用戶的所有文章
SELECT * FROM article
WHERE uid IN (SELECT uid FROM user WHERE status = 1);

子查詢**必須回傳單列**。
MySQL 5.6+ 半連接優化：符合條件自動轉為 JOIN，避免物化。
**陷阱**：子查詢回傳含 NULL → NOT IN 全 FALSE (見下)。

2.3 NULL 陷阱完整解析

運算式	子查詢結果	結果	原因
`5 IN (1,2,NULL)`	含 NULL	FALSE	5 不在集合中
`5 NOT IN (1,2,NULL)`	含 NULL	UNKNOWN (0 行)	5 <> NULL → UNKNOWN，AND 鏈導致整體 UNKNOWN
`5 IN (SELECT NULL)`	單一 NULL	FALSE	同左
`5 NOT IN (SELECT NULL)`	單一 NULL	UNKNOWN	同左

**修正**：WHERE col NOT IN (SELECT col FROM t WHERE col IS NOT NULL) 或改用 NOT EXISTS。

2.4 IN vs OR vs JOIN 效能

-- 方案 A: IN 常數列表 (短列表 < 100)
WHERE uid IN (1,2,3,5,8)

-- 方案 B: OR 等價
WHERE uid=1 OR uid=2 OR uid=3 OR uid=5 OR uid=8

-- 方案 C: JOIN 臨時表/衍生表 (長列表 > 1000)
CREATE TEMPORARY TABLE tmp_ids (uid INT PRIMARY KEY);
INSERT INTO tmp_ids VALUES (1),(2),...;
SELECT a.* FROM article a INNER JOIN tmp_ids t ON a.uid = t.uid;

**經驗法則**：
- 短常數列表：IN 優化器內建優化 (二分搜尋)，效能最好。
- 長列表/動態列表：臨時表 + JOIN，避免 SQL 過長、解析耗時、優化器壓力。
- 子查詢列表：IN 可讀性佳，EXISTS 效能更穩定 (見前文)。

三、綜合實戰：複雜篩選條件組合

-- 找出 2024 年 Q1、狀態正常、且為 VIP 或 內部員工的用戶訂單
SELECT o.*
FROM orders o
INNER JOIN user u ON o.uid = u.uid
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-04-01'
  AND u.status = 1
  AND (u.user_type IN ('VIP', 'STAFF') OR u.dept_id IN (SELECT id FROM dept WHERE code='INTERNAL'));

結語： BETWEEN 適合連續範圍 (數值、時間)，**務必用半開半閉區間處理時間**；IN 適合離散集合，**警惕 NULL 導致 NOT IN 失效**。配合 EXPLAIN 驗證 type=range/ref、Extra=Using index condition，選擇最優寫法。