MySQL BETWEEN 用法与实例

本文介绍了在 MySQL 数据库中，如何使用 BETWEEN 运算符确定一个值是否介于某两个值之间。

BETWEEN 运算符确定一个值是否介于某两个值之间。BETWEEN 运算符常用于比较数字和日期类型的数据。

MySQL BETWEEN 语法

BETWEEN 运算符是一个三目运算符，它需要 3 个操作数。下面 BETWEEN 运算符的语法：

expression BETWEEN min AND max
expression NOT BETWEEN min AND max

使用说明：

  • expression 可以是一个字段名、值或其他的表达式（比如函数调用、运算等）。
  • min 是范围的最小值（包含）。
  • max 是范围的最大值（包含）。
  • AND 是一个固定的连接词，用来连接 min 和 max。
  • NOT 是否定运算符，NOT BETWEEN 表达的含义与 BETWEEN 相反。

BETWEEN 运算符相当于是下面写法的简写形式：

expression >= min AND expression <= max

NOT BETWEEN 运算符相当于是下面写法的简写形式：

expression < min OR expression > max

MySQL BETWEEN 运算规则

当字段的值或者给定的值介于 min 和 max 两个值之间时，BETWEEN 运算符返回 1。否则，返回 0。

BETWEEN 运算规则如下：

  • 如果左侧的 expression 介于 min 和 max 之间时， BETWEEN 返回 1，否则返回 0。

    SELECT
    1 BETWEEN 1 AND 3,
    2 BETWEEN 1 AND 3,
    3 BETWEEN 1 AND 3,
    4 BETWEEN 1 AND 3;

    +-------------------+-------------------+-------------------+-------------------+
| 1 BETWEEN 1 AND 3 | 2 BETWEEN 1 AND 3 | 3 BETWEEN 1 AND 3 | 4 BETWEEN 1 AND 3 |
+-------------------+-------------------+-------------------+-------------------+
|                 1 |                 1 |                 1 |                 0 |
+-------------------+-------------------+-------------------+-------------------+

  • 如果 expression, min, max 至少有一个 NULL 时， BETWEEN 运算符的返回 NULL。

    SELECT
    NULL BETWEEN 1 AND 3,
    1 BETWEEN NULL AND 3,
    1 BETWEEN 1 AND NULL;

    +----------------------+----------------------+----------------------+
| NULL BETWEEN 1 AND 3 | 1 BETWEEN NULL AND 3 | 1 BETWEEN 1 AND NULL |
+----------------------+----------------------+----------------------+
|                 NULL |                 NULL |                 NULL |
+----------------------+----------------------+----------------------+

MySQL BETWEEN 实例

BETWEEN 运算符常用于数字和日期类型的数据之间的比较。我们看 1 个实际的例子。

在以下实例中，我们使用 Sakila 示例数据库中的 film 表作为演示。

BETWEEN

以下 SQL 语句使用 BETWEEN 运算符检索影片赔偿价格介于 1 和 10 之间的影片：

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost BETWEEN 1 AND 10;

+---------+------------------------+------------------+
| film_id | title                  | replacement_cost |
+---------+------------------------+------------------+
|      23 | ANACONDA CONFESSIONS   |             9.99 |
|     150 | CIDER DESIRE           |             9.99 |
|     182 | CONTROL ANTHEM         |             9.99 |
|     203 | DAISY MENAGERIE        |             9.99 |
|     221 | DELIVERANCE MULHOLLAND |             9.99 |
|     260 | DUDE BLINDNESS         |             9.99 |
|     272 | EDGE KISSING           |             9.99 |
|     281 | ENCINO ELF             |             9.99 |
|     299 | FACTORY DRAGON         |             9.99 |
|     307 | FELLOWSHIP AUTUMN      |             9.99 |
|     348 | GANDHI KWAI            |             9.99 |
|     389 | GUNFIGHTER MUSSOLINI   |             9.99 |
|     409 | HEARTBREAKERS BRIGHT   |             9.99 |
|     476 | JASON TRAP             |             9.99 |
|     501 | KISSING DOLLS          |             9.99 |
|     522 | LIFE TWISTED           |             9.99 |
|     551 | MAIDEN HOME            |             9.99 |
|     565 | MATRIX SNOWMAN         |             9.99 |
|     572 | METROPOLIS COMA        |             9.99 |
|     623 | NEWTON LABYRINTH       |             9.99 |
|     627 | NORTH TEQUILA          |             9.99 |
|     629 | NOTORIOUS REUNION      |             9.99 |
|     639 | OPPOSITE NECKLACE      |             9.99 |
|     656 | PAPI NECKLACE          |             9.99 |
|     662 | PATHS CONTROL          |             9.99 |
|     686 | PLUTO OLEANDER         |             9.99 |
|     705 | PURPLE MOVIE           |             9.99 |
|     747 | ROXANNE REBEL          |             9.99 |
|     841 | STAR OPERATION         |             9.99 |
|     845 | STEPMOM DREAM          |             9.99 |
|     846 | STING PERSONAL         |             9.99 |
|     863 | SUN CONFESSIONS        |             9.99 |
|     875 | TALENTED HOMICIDE      |             9.99 |
|     880 | TELEMARK HEARTBREAKERS |             9.99 |
|     886 | THEORY MERMAID         |             9.99 |
|     888 | THIN SAGEBRUSH         |             9.99 |
|     912 | TROJAN TOMORROW        |             9.99 |
|     915 | TRUMAN CRAZY           |             9.99 |
|     931 | VALENTINE VANISHING    |             9.99 |
|     953 | WAIT CIDER             |             9.99 |
|     996 | YOUNG LANGUAGE         |             9.99 |
+---------+------------------------+------------------+

此 SQL 语句与下面不使用 BETWEEN 的语句的结果相同：

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost >= 1 AND replacement_cost <= 10;

NOT BETWEEN

以下 SQL 语句使用 BETWEEN 运算符检索影片赔偿价格不介于 1 和 10 之间的影片：

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost NOT BETWEEN 10 AND 50;

+---------+------------------------+------------------+
| film_id | title                  | replacement_cost |
+---------+------------------------+------------------+
|      23 | ANACONDA CONFESSIONS   |             9.99 |
|     150 | CIDER DESIRE           |             9.99 |
|     182 | CONTROL ANTHEM         |             9.99 |
|     203 | DAISY MENAGERIE        |             9.99 |
|     221 | DELIVERANCE MULHOLLAND |             9.99 |
|     260 | DUDE BLINDNESS         |             9.99 |
|     272 | EDGE KISSING           |             9.99 |
|     281 | ENCINO ELF             |             9.99 |
|     299 | FACTORY DRAGON         |             9.99 |
|     307 | FELLOWSHIP AUTUMN      |             9.99 |
|     348 | GANDHI KWAI            |             9.99 |
|     389 | GUNFIGHTER MUSSOLINI   |             9.99 |
|     409 | HEARTBREAKERS BRIGHT   |             9.99 |
|     476 | JASON TRAP             |             9.99 |
|     501 | KISSING DOLLS          |             9.99 |
|     522 | LIFE TWISTED           |             9.99 |
|     551 | MAIDEN HOME            |             9.99 |
|     565 | MATRIX SNOWMAN         |             9.99 |
|     572 | METROPOLIS COMA        |             9.99 |
|     623 | NEWTON LABYRINTH       |             9.99 |
|     627 | NORTH TEQUILA          |             9.99 |
|     629 | NOTORIOUS REUNION      |             9.99 |
|     639 | OPPOSITE NECKLACE      |             9.99 |
|     656 | PAPI NECKLACE          |             9.99 |
|     662 | PATHS CONTROL          |             9.99 |
|     686 | PLUTO OLEANDER         |             9.99 |
|     705 | PURPLE MOVIE           |             9.99 |
|     747 | ROXANNE REBEL          |             9.99 |
|     841 | STAR OPERATION         |             9.99 |
|     845 | STEPMOM DREAM          |             9.99 |
|     846 | STING PERSONAL         |             9.99 |
|     863 | SUN CONFESSIONS        |             9.99 |
|     875 | TALENTED HOMICIDE      |             9.99 |
|     880 | TELEMARK HEARTBREAKERS |             9.99 |
|     886 | THEORY MERMAID         |             9.99 |
|     888 | THIN SAGEBRUSH         |             9.99 |
|     912 | TROJAN TOMORROW        |             9.99 |
|     915 | TRUMAN CRAZY           |             9.99 |
|     931 | VALENTINE VANISHING    |             9.99 |
|     953 | WAIT CIDER             |             9.99 |
|     996 | YOUNG LANGUAGE         |             9.99 |
+---------+------------------------+------------------+

此 SQL 语句与下面不使用 NOT BETWEEN 的语句的结果相同：

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost < 10 OR replacement_cost > 50;

结论

本文通过实例介绍了 MySQL 中 BETWEEN 运算符的语法和用法。 BETWEEN 运算符的用法要点如下：

  • BETWEEN 运算符来测试一个值是否位于一个数值区间或者时间区间内。
  • BETWEEN min AND max 中 min 是区间的最小值，max 是区间的最大值。
  • expression BETWEEN min AND max 与 expression >= min AND expression <= max 结果相同。
  • BETWEEN 运算符可以结合 NOT 运算符进行否定操作。
  • expression NOT BETWEEN min AND max 与 expression < min OR expression > max 结果相同。