萬盛學電腦網

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

SQL BETWEEN 操作符深度解析:範圍查詢的語法、陷阱與跨類型實戰

BETWEEN ... AND:不只是數值範圍的「瑞士刀」

在 SQL 中,BETWEEN ... AND 是最直觀的範圍過濾語法,支援數值、字串、日期時間等多種資料類型。然而,不同資料庫對邊界包含策略、字串排序規則的處理存在差異,稍有不慎便會導致資料遺漏或多查。本文系統整理語法細節、實戰範例、跨資料庫差異與效能優化建議。

一、標準語法與核心特性

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
  • 等價轉換:col BETWEEN a AND b ⇔ col >= a AND col <= b(標準 SQL 定義為閉區間,即包含端點)。
  • 類型自動轉換:MySQL 會嘗試將 value1/value2 隱式轉換為 column_name 的類型;建議顯式使用 CAST 避免隱式轉換導致索引失效。
  • NULL 語義:若 column_name 為 NULL,或 value1/value2 含 NULL,結果為 UNKNOWN(不會出現在結果集)。

二、三大類型實戰範例(基於 Employee 表)

測試表結構:id, first_name, last_name, start_date, end_date, salary, city, description

1. 數值範圍:薪資區間查詢

SELECT * FROM Employee
WHERE salary BETWEEN 1000 AND 2000;

返回薪資在 1000~2000 之間的員工(包含 1000 和 2000)。若需開區間,改用 salary > 1000 AND salary < 2000。

2. 字串範圍:名字字母序區間

SELECT * FROM Employee
WHERE first_name BETWEEN 'Elvis' AND 'Michael';

關鍵細節:字串比較依賴 Collation(排序規則)。預設 utf8mb4_general_ci 不區分大小寫,'elvis' 到 'michael' 之間的名字皆會命中。若需區分大小寫,指定 COLLATE utf8mb4_bin。

3. 日期範圍:入職時間窗口

SELECT * FROM Employee
WHERE start_date BETWEEN '1990-01-01' AND '1999-12-31';

日期字面量建議使用 ISO 8601 格式(YYYY-MM-DD),避免因 sql_mode 或地區設定導致解析錯誤。

三、NOT BETWEEN 與複合條件

-- 查詢「可用庫存」不在 10~20 之間的主題
SELECT Name, InStock, OnOrder, Reserved
FROM Topic
WHERE (InStock + OnOrder - Reserved) NOT BETWEEN 10 AND 20
ORDER BY Name;

NOT BETWEEN 等價於 value < low OR value > high,注意 NULL 值仍不會被選中。

四、跨資料庫的「邊界包含」陷阱 ⚠️

資料庫BETWEEN 行為備註
MySQL / PostgreSQL / SQL Server / Oracle閉區間 [low, high]標準 SQL 行為
SQLite閉區間 [low, high]同上
部分舊版本 / 特定設定可能為左閉右開 [low, high)極少見,需實測驗證

字串邊界的隱形坑:某些資料庫對 'Adams' AND 'Carter' 的處理不同:

  • 包含 'Adams'、不含 'Carter'
  • 兩者皆包含
  • 兩者皆不包含

防禦性寫法:字串範圍查詢建議改用顯式比較:
WHERE last_name >= 'Adams' AND last_name < 'Carter'(左閉右開,語義無歧義)。

五、效能優化與索引利用

  1. 索引友好:BETWEEN 可觸發 Range Scan,前提是欄位有索引且無隱式轉換。
  2. 避免函數包裹:WHERE DATE(created_at) BETWEEN ... 會導致全表掃描,改為 WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01'。
  3. 大範圍慎用:若範圍覆蓋 >20% 資料,優化器可能放棄索引轉全表掃描;可考慮分區表或摘要表。

六、最佳實踐清單

  • ✅ 數值、日期類型放心用 BETWEEN,語義清晰。
  • ⚠️ 字串範圍改用 >= AND < 明確邊界語義。
  • ✅ 字面量與欄位類型一致,避免隱式轉換。
  • ✅ 複合條件用括號明確優先級:WHERE (score BETWEEN 60 AND 80) AND status = 1。
  • 📝 文檔註釋說明邊界策略,便於團隊維護。

掌握 BETWEEN 的邊界語義與類型轉換規則,能讓你在撰寫報表、儀表板、資料清洗腳本時少踩無數坑。下次寫範圍查詢前,先問自己:「這裡的端點,到底算不算在裡面?」

copyright © 萬盛學電腦網 all rights reserved