> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL BETWEEN

> MySQL 的 BETWEEN 运算符如何测试一个范围，为什么两个边界都包含在内，为什么下界必须写在前面，以及它悄悄算错的日期范围。

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

`BETWEEN` 测试一个值是否落在一个范围内。它是一对比较的简写，也是人们在日期上最容易出微妙错误的子句。

开始之前，请用 `mysql -u root -p sakila` 打开客户端。

## BETWEEN 的工作原理

```sql theme={null}
SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME BETWEEN LOW_BOUND AND HIGH_BOUND;
```

把 `LOW_BOUND` 和 `HIGH_BOUND` 换成范围的 2 个端点。

`x BETWEEN a AND b` 的意思是 `x >= a AND x <= b`。范围包含两个边界，而且下界要写在前面。

## 示例

### 一个数字范围

```sql theme={null}
SELECT title, length
FROM film
WHERE length BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+-----------------------+--------+
| title                 | length |
+-----------------------+--------+
| BUBBLE GROSSE         |     60 |
| JERSEY SASSY          |     60 |
| MOCKINGBIRD HOLLYWOOD |     60 |
| PHANTOM GLORY         |     60 |
| PITY BOUND            |     60 |
+-----------------------+--------+
5 rows in set (0.00 sec)
```

时长恰好 60 分钟的电影在结果之中，这说明下界算作匹配。

### 同样的意思完整写出

```sql theme={null}
SELECT title, length
FROM film
WHERE length >= 60 AND length <= 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+-----------------------+--------+
| title                 | length |
+-----------------------+--------+
| BUBBLE GROSSE         |     60 |
| JERSEY SASSY          |     60 |
| MOCKINGBIRD HOLLYWOOD |     60 |
| PHANTOM GLORY         |     60 |
| PITY BOUND            |     60 |
+-----------------------+--------+
5 rows in set (0.00 sec)
```

完全相同的行。`BETWEEN` 让您少写一次列名，除此之外别无其他。

### 范围之外

```sql theme={null}
SELECT title, length
FROM film
WHERE length NOT BETWEEN 60 AND 70
ORDER BY length, title
LIMIT 5;
```

```text theme={null}
+---------------------+--------+
| title               | length |
+---------------------+--------+
| ALIEN CENTER        |     46 |
| IRON MOON           |     46 |
| KWAI HOMEWARD       |     46 |
| LABYRINTH LEAGUE    |     46 |
| RIDGEMONT SUBMARINE |     46 |
+---------------------+--------+
5 rows in set (0.01 sec)
```

`NOT BETWEEN` 保留低于下界和高于上界的行。46 分钟是整个目录中最短的电影。

## 下界必须写在前面

`BETWEEN` 不会替您给边界排序。先给它一个大的值，它就要求数字既至少为 70 又至多为 60，没有任何数字能满足。

```sql theme={null}
SELECT title, length
FROM film
WHERE length BETWEEN 70 AND 60
ORDER BY length, title;
```

```text theme={null}
Empty set (0.00 sec)
```

没有错误，也没有警告。一个您本来预期有匹配的范围返回了空结果，值得先检查一下边界是否写反了。

## 日期上的 BETWEEN 止于午夜

这是真正让人浪费时间的那一个，而它从列的类型开始。

```sql theme={null}
SHOW COLUMNS FROM payment LIKE 'payment_date';
```

```text theme={null}
+--------------+----------+------+-----+---------+-------+
| Field        | Type     | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------+
| payment_date | datetime | NO   |     | NULL    |       |
+--------------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
```

`payment_date` 是 `datetime`，所以每个值都带有一天中的时刻。一个不带时间的日期表示那一天开始时的午夜。

```sql theme={null}
SELECT payment_id
FROM payment
WHERE payment_date BETWEEN '2005-05-25' AND '2005-05-26'
ORDER BY payment_id;
```

```text theme={null}
+------------+
| payment_id |
+------------+
|          1 |
|        146 |
|        174 |
...
+------------+
137 rows in set (0.01 sec)
```

这读起来是"25 日和 26 日"，返回 137 行。用半开区间请求同样的 2 天，返回 311 行。

```sql theme={null}
SELECT payment_id
FROM payment
WHERE payment_date >= '2005-05-25' AND payment_date < '2005-05-27'
ORDER BY payment_id;
```

```text theme={null}
+------------+
| payment_id |
+------------+
|          1 |
|        146 |
|        174 |
...
+------------+
311 rows in set (0.00 sec)
```

上界 `'2005-05-26'` 变成了 `2005-05-26 00:00:00`。26 日当天收到的每一笔付款都晚于这一时刻，所以 `BETWEEN` 把它们全部排除了。那 137 行只是 25 日一天的，如果有一笔付款恰好落在午夜整点，它们也会把它包括进去。

请把日期范围写成 `>= 起始日 AND < 结束日的后一天`。这种形式不需要考虑列是否带有时间，而且在列后来加上时间时仍然正确。

## 总结

* 用 `BETWEEN` 表示一个范围，并预期它包含两个边界。
* 把下界写在前面，因为反过来的范围什么都不匹配，也什么都不说。
* 把 `x BETWEEN a AND b` 读作 `x >= a AND x <= b`。
* 对日期使用 `>= start AND < day_after_end`，而不是 `BETWEEN`。

## 另请参阅

* [WHERE](https://villagesql.com/docs/zh/tutorial/where.md) — BETWEEN 所在的子句
* [运算符](https://villagesql.com/docs/zh/tutorial/operators.md) — BETWEEN 所简写的那些比较
* [IN](https://villagesql.com/docs/zh/tutorial/in.md) — 测试一个值列表而不是一个范围
* [MySQL 中的日期和时间函数](https://villagesql.com/docs/zh/guides/date-time-functions.md) — 构造范围的边界


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.