MySQL BETWEEN 语法及示例教程详解
在数据库查询中，我们经常需要查找某个字段的值落在特定范围内的数据。例如，查找价格在 50 到 100 元之间的商品，或者查找在某个日期区间内创建的订单。MySQL 提供了 BETWEEN 运算符，可以非常方便地处理这类范围查询。
本文将详细介绍 MySQL 中 BETWEEN 语法的用法、工作原理，并通过丰富的示例来帮助你掌握如何在不同数据类型（数字、日期、字符串）上使用 BETWEEN，以及相关的注意事项。
1. BETWEEN 语法概述
BETWEEN 运算符用于在 WHERE 子句中筛选出介于两个值之间的数据。它的基本语法如下：
sql
SELECT column_list
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
语法解释：
SELECT column_list: 你想要从表中检索的列。FROM table_name: 你要查询的表名。WHERE: 筛选数据的子句。column_name: 你要进行范围比较的列名。BETWEEN: MySQL 的范围运算符。value1: 范围的起始值（下限）。AND: 用于连接value1和value2的关键字。value2: 范围的结束值（上限）。
重要特性：
BETWEEN value1 AND value2 是包含范围的，这意味着它会选取 column_name 的值大于或等于 value1 并且小于或等于 value2 的所有行。
换句话说，以下两个条件表达式是等价的：
sql
column_name BETWEEN value1 AND value2
等价于：
sql
column_name >= value1 AND column_name <= value2
2. BETWEEN 工作原理
BETWEEN 运算符实际上是 >= 和 <= 两个条件的逻辑组合 (AND) 的语法糖。当 MySQL 解析 BETWEEN value1 AND value2 时，它会将其转换为 column_name >= value1 AND column_name <= value2 来执行。
这种转换对于理解 BETWEEN 的行为至关重要，尤其是在处理日期和字符串类型时。
虽然功能上等价于 >= AND <=，但 BETWEEN 在表达“位于某个范围内”这个意图时，通常更简洁易读，特别是在处理简单的数值或日期范围时。
3. BETWEEN 语法示例 (使用示例表)
为了更好地演示 BETWEEN 的用法，我们创建一个示例表 products 并插入一些数据。
“`sql
— 创建示例表
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT NOT NULL,
production_date DATE,
last_updated DATETIME
);
— 插入示例数据
INSERT INTO products (name, price, stock, production_date, last_updated) VALUES
(‘Laptop’, 1200.00, 50, ‘2023-01-15’, ‘2023-01-15 10:00:00’),
(‘Mouse’, 25.00, 200, ‘2023-02-20’, ‘2023-02-20 11:30:00’),
(‘Keyboard’, 75.00, 150, ‘2023-03-10’, ‘2023-03-10 14:00:00’),
(‘Monitor’, 300.00, 80, ‘2023-04-05’, ‘2023-04-05 09:15:00’),
(‘Webcam’, 60.00, 120, ‘2023-05-25’, ‘2023-05-25 16:45:00’),
(‘Printer’, 180.00, 30, ‘2023-06-01’, ‘2023-06-01 10:00:00’),
(‘Speaker’, 95.00, 90, ‘2023-07-18’, ‘2023-07-18 13:20:00’),
(‘Router’, 150.00, 70, ‘2023-08-30’, ‘2023-08-30 15:00:00’),
(‘External SSD’, 110.00, 65, ‘2023-09-12’, ‘2023-09-12 11:00:00’),
(‘USB Drive’, 15.00, 300, ‘2023-10-01’, ‘2023-10-01 09:00:00’),
(‘Headphones’, 55.00, 180, ‘2023-11-05’, ‘2023-11-05 14:30:00’),
(‘Microphone’, 85.00, 100, ‘2023-12-01’, ‘2023-12-01 10:00:00’),
(‘Desk Lamp’, 40.00, 220, ‘2024-01-10’, ‘2024-01-10 11:00:00’),
(‘Smartwatch’, 250.00, 40, ‘2024-02-15’, ‘2024-02-15 15:00:00’),
(‘Gaming Chair’, 350.00, 25, ‘2024-03-20’, ‘2024-03-20 16:00:00’),
(‘Drawing Tablet’, 130.00, 35, ‘2024-04-01’, ‘2024-04-01 09:00:00’),
(‘Wireless Charger’, 30.00, 180, ‘2024-05-10’, ‘2024-05-10 10:00:00’),
(‘Bluetooth Speaker’, 105.00, 75, ‘2024-06-05’, ‘2024-06-05 14:00:00’);
“`
现在我们可以使用这个 products 表来演示 BETWEEN 的不同用法。
3.1 数值类型示例 (DECIMAL 和 INT)
使用 BETWEEN 查找价格在 50.00 到 100.00 之间的产品（包含 50.00 和 100.00）：
sql
SELECT id, name, price
FROM products
WHERE price BETWEEN 50.00 AND 100.00;
查询结果预期（部分）：
| id | name | price |
| --- | ------------ | ------ |
| 3 | Keyboard | 75.00 |
| 5 | Webcam | 60.00 |
| 7 | Speaker | 95.00 |
| 11 | Headphones | 55.00 |
| 12 | Microphone | 85.00 |
使用 BETWEEN 查找库存量在 100 到 200 之间的产品（包含 100 和 200）：
sql
SELECT id, name, stock
FROM products
WHERE stock BETWEEN 100 AND 200;
查询结果预期（部分）：
| id | name | stock |
| --- | ---------- | ----- |
| 2 | Mouse | 200 |
| 3 | Keyboard | 150 |
| 5 | Webcam | 120 |
| 7 | Speaker | 90 | -- Oh, wait, 90 is not >= 100. Let's recheck my data or query. The data has Speaker at 90. My query is correct, the expected result should only include stock >= 100 AND <= 200.
| 11 | Headphones | 180 |
| 12 | Microphone | 100 |
Corrected Expected Result:
| id | name | stock |
| --- | ---------- | ----- |
| 2 | Mouse | 200 |
| 3 | Keyboard | 150 |
| 5 | Webcam | 120 |
| 11 | Headphones | 180 |
| 12 | Microphone | 100 |
这个例子清晰地展示了 BETWEEN 在数值类型上的用法，它会筛选出落在指定闭区间内的所有数值。
3.2 日期和时间类型示例 (DATE 和 DATETIME)
BETWEEN 在日期和时间类型上也非常有用。
使用 DATE 类型：
查找生产日期在 ‘2023-06-01’ 到 ‘2023-12-31’ 之间的产品（包含这两天）：
sql
SELECT id, name, production_date
FROM products
WHERE production_date BETWEEN '2023-06-01' AND '2023-12-31';
查询结果预期：
| id | name | production_date |
| --- | ------------ | --------------- |
| 6 | Printer | 2023-06-01 |
| 7 | Speaker | 2023-07-18 |
| 8 | Router | 2023-08-30 |
| 9 | External SSD | 2023-09-12 |
| 10 | USB Drive | 2023-10-01 |
| 11 | Headphones | 2023-11-05 |
| 12 | Microphone | 2023-12-01 |
这里，BETWEEN 正确地包含了起始日期和结束日期。
使用 DATETIME 或 TIMESTAMP 类型：
这是使用 BETWEEN 处理时间范围时需要特别注意的地方。
假设我们想查找在 ‘2023-06-01’ 到 ‘2023-12-31’ 期间发生的任何更新的产品（使用 last_updated 列）。
sql
SELECT id, name, last_updated
FROM products
WHERE last_updated BETWEEN '2023-06-01' AND '2023-12-31';
查询结果预期（部分）：
| id | name | last_updated |
| --- | ------------ | ------------------- |
| 6 | Printer | 2023-06-01 10:00:00 |
| 7 | Speaker | 2023-07-18 13:20:00 |
| 8 | Router | 2023-08-30 15:00:00 |
| 9 | External SSD | 2023-09-12 11:00:00 |
| 10 | USB Drive | 2023-10-01 09:00:00 |
| 11 | Headphones | 2023-11-05 14:30:00 |
| 12 | Microphone | 2023-12-01 10:00:00 |
注意到这里的结果只包含了到 ‘2023-12-31’ 的 00:00:00 为止的记录。这是因为 BETWEEN 比较的是完整的 DATETIME 或 TIMESTAMP 值，而 '2023-12-31' 默认会被解析为 '2023-12-31 00:00:00'。
如果你想包含 ‘2023-12-31’ 整天的记录，你需要将结束时间指定为当天的最后一刻，例如 '2023-12-31 23:59:59' 或更精确的时间。
sql
SELECT id, name, last_updated
FROM products
WHERE last_updated BETWEEN '2023-06-01 00:00:00' AND '2023-12-31 23:59:59';
或者，更推荐的方式是使用 >= 和 < 结合的方式，将范围设定为从起始日期的开始到结束日期的下一天的开始。这种方法可以避免精确到秒甚至更小单位的问题。
sql
SELECT id, name, last_updated
FROM products
WHERE last_updated >= '2023-06-01' AND last_updated < '2024-01-01'; -- '2024-01-01' is the start of the day after '2023-12-31'
这两种方法（指定到最后一秒或使用 < 下一天的开始）都能正确地包含结束日期的所有时间点，但在处理 DATETIME/TIMESTAMP 时，理解 BETWEEN 包含的是精确的时间点非常重要。
3.3 字符串类型示例
BETWEEN 也可以用于字符串类型，它会根据数据库的字符集和排序规则（Collation）进行字典序（字母顺序）比较。
查找名字在 ‘M’ 和 ‘R’ 之间的产品（包含以 ‘M’ 开头和以 ‘R’ 开头的产品，以及其他在字典序上介于 ‘M’ 和 ‘R’ 之间的产品）：
sql
SELECT id, name
FROM products
WHERE name BETWEEN 'M' AND 'R';
查询结果预期：
| id | name |
| --- | ---------- |
| 2 | Mouse |
| 12 | Microphone |
| 4 | Monitor |
| 6 | Printer | -- Note: 'Printer' is between 'M' and 'R' in lexicographical order.
| 7 | Speaker | -- Note: 'Speaker' is *not* between 'M' and 'R'. 'S' comes after 'R'. My data/logic check needed again.
| 8 | Router |
Corrected Explanation and Expected Result:
字符串的 BETWEEN 'value1' AND 'value2' 比较的是整个字符串。它会返回所有字典序上大于或等于 'value1' 且小于或等于 'value2' 的字符串。
例如，name BETWEEN 'M' AND 'R' 意味着：
* 'Mouse' >= 'M' (True) AND 'Mouse' <= 'R' (True) -> 包含
* 'Monitor' >= 'M' (True) AND 'Monitor' <= 'R' (True) -> 包含
* 'Microphone' >= 'M' (True) AND 'Microphone' <= 'R' (True) -> 包含
* 'Printer' >= 'M' (True) AND 'Printer' <= 'R' (True) -> 包含 (‘P’ 在 ‘M’ 和 ‘R’ 之间)
* 'Router' >= 'M' (True) AND 'Router' <= 'R' (True) -> 包含 (‘R’ 是上限，包含)
* 'Speaker' >= 'M' (True) AND 'Speaker' <= 'R' (False,因为 ‘S’ > ‘R’) -> 不包含
所以，正确的查询结果预期是：
| id | name |
| --- | ---------- |
| 2 | Mouse |
| 12 | Microphone |
| 4 | Monitor |
| 6 | Printer |
| 8 | Router |
请注意，如果你的目的是查找以特定字母范围开头的字符串（例如，以 ‘M’ 到 ‘R’ 之间的字母开头的），BETWEEN 可能不是最直观的方法。使用 LIKE 和通配符结合 OR 或 REGEXP 可能更符合你的需求：
sql
-- 查找以 M, N, O, P, Q, R 开头的名字
SELECT id, name
FROM products
WHERE name LIKE 'M%' OR name LIKE 'N%' OR name LIKE 'O%'
OR name LIKE 'P%' OR name LIKE 'Q%' OR name LIKE 'R%';
然而，如果你的字符串是像产品编号（如 A100 到 A200）或者按照某种规则递增的序列，BETWEEN 可以非常方便地进行范围查询。
查找名称在 ‘E’ 和 ‘K’ 之间的产品：
sql
SELECT id, name
FROM products
WHERE name BETWEEN 'E' AND 'K';
查询结果预期：
| id | name |
| --- | ---------- |
| 9 | External SSD | -- 'External SSD' is after 'E' and before 'K'
4. NOT BETWEEN 语法
与 BETWEEN 相反，NOT BETWEEN 运算符用于筛选出值不在指定范围内的行。
它的基本语法是：
sql
SELECT column_list
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;
NOT BETWEEN value1 AND value2 等价于：
sql
column_name < value1 OR column_name > value2
NOT BETWEEN 示例：
查找价格不在 50.00 到 100.00 之间的产品：
sql
SELECT id, name, price
FROM products
WHERE price NOT BETWEEN 50.00 AND 100.00;
查询结果预期：
| id | name | price |
| --- | ---------------- | ------- |
| 1 | Laptop | 1200.00 | -- > 100
| 2 | Mouse | 25.00 | -- < 50
| 4 | Monitor | 300.00 | -- > 100
| 6 | Printer | 180.00 | -- > 100
| 8 | Router | 150.00 | -- > 100
| 9 | External SSD | 110.00 | -- > 100
| 10 | USB Drive | 15.00 | -- < 50
| 13 | Desk Lamp | 40.00 | -- < 50
| 14 | Smartwatch | 250.00 | -- > 100
| 15 | Gaming Chair | 350.00 | -- > 100
| 16 | Drawing Tablet | 130.00 | -- > 100
| 17 | Wireless Charger | 30.00 | -- < 50
| 18 | Bluetooth Speaker| 105.00 | -- > 100
查找生产日期不在 ‘2023-06-01’ 到 ‘2023-12-31’ 之间的产品：
sql
SELECT id, name, production_date
FROM products
WHERE production_date NOT BETWEEN '2023-06-01' AND '2023-12-31';
查询结果预期：
| id | name | production_date |
| --- | -------------- | --------------- |
| 1 | Laptop | 2023-01-15 | -- < '2023-06-01'
| 2 | Mouse | 2023-02-20 | -- < '2023-06-01'
| 3 | Keyboard | 2023-03-10 | -- < '2023-06-01'
| 4 | Monitor | 2023-04-05 | -- < '2023-06-01'
| 5 | Webcam | 2023-05-25 | -- < '2023-06-01'
| 13 | Desk Lamp | 2024-01-10 | -- > '2023-12-31'
| 14 | Smartwatch | 2024-02-15 | -- > '2023-12-31'
| 15 | Gaming Chair | 2024-03-20 | -- > '2023-12-31'
| 16 | Drawing Tablet | 2024-04-01 | -- > '2023-12-31'
| 17 | Wireless Charger| 2024-05-10 | -- > '2023-12-31'
| 18 | Bluetooth Speaker| 2024-06-05 | -- > '2023-12-31'
5. 使用 BETWEEN 的注意事项
使用 BETWEEN 虽然方便，但需要注意一些细节，以避免潜在的问题：
- 数据类型一致性：
BETWEEN比较的列和两个值（value1和value2）应该具有相同或兼容的数据类型。例如，不要将数字与日期或字符串进行比较。MySQL 会尝试进行类型转换，但这可能导致意外的结果或性能问题。 - 值的顺序： 在大多数 SQL 方言中，
BETWEEN value1 AND value2要求value1必须小于或等于value2(value1 <= value2)。虽然 MySQL 在某些版本和情况下会智能地交换value1和value2如果它们的顺序错误，但强烈建议始终保持value1 <= value2这个顺序。这不仅是良好的实践，也能确保你的查询在其他数据库系统中表现一致。 -
NULL值的处理：BETWEEN和NOT BETWEEN在处理NULL值时遵循 SQL 的三值逻辑（True, False, Unknown/NULL）。如果column_name的值为NULL，则column_name BETWEEN value1 AND value2的结果是NULL(Unknown)，同样column_name NOT BETWEEN value1 AND value2的结果也是NULL。在WHERE子句中，条件结果为NULL的行不会被返回。这意味着如果你的范围列中可能包含NULL值，并且你想包含这些行，你需要额外添加OR column_name IS NULL条件。sql
-- 如果想包含 price 为 NULL 的行
SELECT id, name, price
FROM products
WHERE price BETWEEN 50.00 AND 100.00 OR price IS NULL;
4. 日期和时间精度： 正如日期示例中讨论的，对于DATETIME或TIMESTAMP类型，BETWEEN比较的是完整的时间点。使用'YYYY-MM-DD'格式的日期字符串作为DATETIME列的范围值时，它会被隐式转换为'YYYY-MM-DD 00:00:00'。要包含整个结束日期，请务必指定到该日期的最后一刻，或者使用>=和<结合下一天的开始时间。 -
字符串排序规则：
BETWEEN在字符串上的行为受限于 MySQL 的字符集和排序规则。不同的排序规则可能会导致不同的比较结果（例如，是否区分大小写）。请确保你的表和列使用了适合你需求的排序规则。 -
索引优化：
BETWEEN条件通常能够很好地利用在column_name上创建的索引。由于它等价于>= AND <=，数据库优化器可以高效地使用 B-tree 索引来快速定位范围的起始点和结束点，从而提高查询性能。
6. 总结
MySQL BETWEEN 运算符是用于在 WHERE 子句中进行范围查询的便捷工具。它能够筛选出某个字段值位于指定闭区间（包含起始值和结束值）的所有记录。
- 基本语法：
WHERE column_name BETWEEN value1 AND value2 - 等价于：
WHERE column_name >= value1 AND column_name <= value2 NOT BETWEEN用于筛选范围外的数据，等价于< value1 OR > value2。- 适用于数值、日期/时间、字符串等数据类型。
- 使用时需注意数据类型一致性、值的顺序（建议始终下限在前）、NULL 值的处理以及日期/时间精度问题。
在编写 SQL 查询时，当需要查找某个范围的数据时，优先考虑使用 BETWEEN 运算符，因为它通常比使用 >= AND <= 组合更具可读性。理解其工作原理和注意事项，特别是日期时间的处理，将帮助你写出更准确和高效的 SQL 语句。
希望这篇详细的教程对你理解和使用 MySQL 的 BETWEEN 语法有所帮助！