萬盛學電腦網

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

SQL BETWEEN 範圍查詢完整教學:數值/日期/字串範圍、邊界包含與效能優化

BETWEEN 基礎語法

SELECT column_list
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

等同於:column_name >= value1 AND column_name <= value2 (包含邊界值)。

適用資料型別

型別範例說明
數值WHERE price BETWEEN 100 AND 500整數、浮點數、DECIMAL
日期/時間WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'DATE、DATETIME、TIMESTAMP
字串WHERE last_name BETWEEN 'Chen' AND 'Wang'按字元集排序規則比較

實戰範例

範例資料:Persons 表

P_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes
2SvendsonToveBorgvn 23Sandnes
3PettersenKariStorgt 20Stavanger

查詢姓氏在 Hansen 到 Pettersen 之間 (按字典序)

SELECT * FROM Persons 
WHERE LastName BETWEEN 'Hansen' AND 'Pettersen';

結果

P_IdLastNameFirstNameAddressCity
1HansenOlaTimoteivn 10Sandnes

注意:Svendson (S) 在 Pettersen (P) 之後,不在範圍內。字典序:H < P < S。

NOT BETWEEN 排除範圍

-- 價格不在 100-500 之間的商品
SELECT * FROM products WHERE price NOT BETWEEN 100 AND 500;

-- 等同於
SELECT * FROM products WHERE price < 100 OR price > 500;

日期範圍查詢最佳實踐

❌ 常見錯誤:BETWEEN 日期時間型別

-- 危險:DATETIME 包含時間部分
WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'
-- 實際等同:created_at >= '2024-01-01 00:00:00' AND created_at <= '2024-01-31 00:00:00'
-- 會漏掉 1月31日 00:00:01 之後的資料!

✅ 正確做法:半開區間 [開始, 結束)

-- 標準寫法:包含開始日,不包含結束日翌日
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'

相對時間範圍

-- 最近 7 天
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)

-- 本月
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
  AND created_at < DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')

-- 上月
WHERE created_at >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND created_at < DATE_FORMAT(NOW(), '%Y-%m-01')

字串範圍查詢與 Collation 關係

-- utf8mb4_general_ci (不區分大小寫,簡易排序)
WHERE name BETWEEN 'a' AND 'm'  -- 包含 A-Z, a-m

-- utf8mb4_bin (二進制,區分大小寫,按碼點)
WHERE name BETWEEN 'a' AND 'm'  -- 僅包含 a-m (ASCII 97-109),不含 A-M (65-77)

索引使用與效能

  • BETWEEN 於索引欄位可走 range 類型索引掃描
  • 等同於 = + <=,優化器處理相同
  • 範圍過大 (如 > 20% 表行數) 優化器可能選擇全表掃描
  • 複合索引最左前綴原則同樣適用:INDEX(a,b) 支援 WHERE a=1 AND b BETWEEN 10 AND 20

進階用法

配合 CASE WHEN 分桶統計

SELECT 
    CASE 
        WHEN score BETWEEN 90 AND 100 THEN '優'
        WHEN score BETWEEN 80 AND 89 THEN '良'
        WHEN score BETWEEN 60 AND 79 THEN '及格'
        ELSE '不及格'
    END AS grade,
    COUNT(*) AS cnt
FROM scores
GROUP BY grade;

時間序列分桶

SELECT 
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS orders
FROM orders
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY month
ORDER BY month;

PHP 實作:安全的日期範圍查詢

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$start = $_GET['start'] ?? date('Y-m-01');
$end = $_GET['end'] ?? date('Y-m-d');

// 驗證日期格式
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $start) || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $end)) {
    die('日期格式錯誤,請用 YYYY-MM-DD');
}

// 計算結束日翌日 (半開區間)
$endNext = date('Y-m-d', strtotime($end . ' +1 day'));

$stmt = $mysqli->prepare("
    SELECT id, order_no, total_amount, created_at
    FROM orders
    WHERE created_at >= ? AND created_at < ?
    ORDER BY created_at DESC
    LIMIT 100
");
$stmt->bind_param('ss', $start, $endNext);
$stmt->execute();
$result = $stmt->get_result();

$data = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($data, JSON_UNESCAPED_UNICODE);

$stmt->close();
$mysqli->close();
?>

總結

  1. BETWEEN 包含邊界值,等同 = + <= + >=
  2. 日期時間型別**避免用 BETWEEN**,改用半開區間 [start, end_next)
  3. 字串範圍受 Collation 影響,注意大小寫敏感度
  4. 索引欄位走 range 掃描,大範圍可能轉全表掃描
  5. PHP 日期參數必須驗證格式,防注入
copyright © 萬盛學電腦網 all rights reserved