BETWEEN ... AND ... 定義閉區間 [low, high],適用於數字、日期、字符串(按排序規則)。本文覆蓋語法、否定形式、索引利用、隱式轉換陷阱、分頁場景應用。
SELECT column_list
FROM table
WHERE column_name [NOT] BETWEEN low_value AND high_value;
low_value 和 high_valueSELECT EmployeeIDNo, Salary
FROM EmployeeStatisticsTable
WHERE Salary BETWEEN 30000 AND 50000; -- 30000 <= Salary <= 50000
SELECT EmployeeIDNo, Salary
FROM EmployeeStatisticsTable
WHERE Salary NOT BETWEEN 30000 AND 50000; -- Salary < 30000 OR Salary > 50000
SELECT *
FROM EmployeeAddressTable
WHERE LastName BETWEEN 'Ackerman' AND 'Scott'; -- A開頭 到 S開頭 之間
SELECT * FROM orders
WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31 23:59:59';
💡 最佳實踐:日期用 >= '2023-01-01' AND < '2024-01-01' 避免時分秒邊界問題。
BETWEEN 於索引列上可用索引範圍掃描(type=range)(a,b):WHERE a=1 AND b BETWEEN 10 AND 20 → 索引下推WHERE DATE(created_at) BETWEEN ... → 全表掃描VARCHAR 存數字,BETWEEN '2' AND '10' 按字典序 → '2' > '10' 導致空結果。修正:CAST(col AS UNSIGNED) BETWEEN 2 AND 10 或改字段類型。BETWEEN '2023-01-01' AND '2023-01-31' 漏掉 31 日 00:00:01 之後的數據。用半開區間 [start, end_next)。-- 第一頁
SELECT * FROM logs WHERE id BETWEEN 1 AND 100 ORDER BY id;
-- 下一頁(知道上頁最大 id=100)
SELECT * FROM logs WHERE id BETWEEN 101 AND 200 ORDER BY id;
優於 OFFSET:無需掃描跳過行,索引直接定位。
BETWEEN 閉區間,下界在前上界後;
數字日期首選,字符串慎用字典序;
索引列上用,函數包裹休想走;
分頁用游標,OFFSET 靠邊站。