迹忆客 专注技术分享

本教程演示如何使用这三种方法查询两个日期之间的数据库表。

在 MySQL 中检索日期范围内的数据

MySQL 通过 date 和 time 数据类型为处理日期提供了一定程度的便利，它们可以组合形成 datetime 或 timestamp。

在使用日期列时，各种比较方法可以与 SELECT 和 WHERE 子句结合使用，以有效地检索日期范围内所需的数据。

1. 使用 BETWEEN 子句。
2. 使用其他比较运算符，如 <、>、<= 和 >=。
3. 使用结合 INNER JOIN 的日期范围生成器。

使用 SELECT、WHERE 和 BETWEEN 子句查询 MySQL 范围内的日期列

SELECT-WHERE-BETWEEN 子句是在 MySQL 中过滤结果集的有效工具。BETWEEN 关键字与本教程最相关。

但是，它需要指定要检索的值的下限和上限。

例如，让我们创建一个名为 registration_db 的示例数据库，其中包含一个包含三列和五个记录的 registered_persons 表。

CREATE DATABASE registration_db;
USE registration_db;

-- CREATE TABLE
CREATE TABLE registered_persons(
	id INT AUTO_INCREMENT,
    name VARCHAR (255),
    date_registered DATE,

    PRIMARY KEY(id)
);
-- POPULATING TABLE
INSERT INTO registered_persons (name, date_registered) VALUES
    ("Mike Hannover","2019-10-24"),
    ("June Popeyes", "2019-10-30"),
    ("David Craigson", "2019-11-02"),
    ("Eleanor Roosenotvelt", "2019-11-03"),
    ("Albert Undsteiner", "2019-11-28");

-- PREVIEW TABLE
SELECT * FROM registered_persons;

输出：

id	name					date_registered
1	Mike Hannover			2019-10-24
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03
5	Albert Undsteiner		2019-11-28

现在，让我们在数据库中查询 2019 年 10 月 25 日至 2019 年 11 月 3 日期间注册的人员的详细信息。

SELECT * FROM registered_persons
WHERE date_registered
BETWEEN "2019-10-25" AND "2019-11-03";

输出：

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

请注意，2019 年 11 月 3 日注册的人已包含在结果集中。我们得到这个是因为 BETWEEN 子句包含下限和上限。

此外，查询日期必须从较低的范围到较高的范围。否则查询将返回空值。

在 MySQL 中使用 >= 和 <= 比较运算符查询一个范围内的日期列

如前所述，比较运算符如 <、>、<= 和 >= 可以复制 BETWEEN 子句的操作。

让我们使用 >= AND <= 复制前面的查询（包括最小和最大范围值，与 BETWEEN 子句一样）。

SELECT * FROM registered_persons
WHERE date_registered >= "2019-10-25" AND date_registered <= "2019-11-03";

/* The WHERE query can also be written in this form to replicate BETWEEN
WHERE "2019-10-25" <= date_registered AND date_registered <= "2019-11-03";
*/

输出：

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

结果和预期的一样。将这些比较运算符用于此类操作的唯一缺点是查询参数的重复，因为 date_registered 重复了两次。

这是比较运算符的官方文档以供进一步参考。

使用递归日期范围生成器方法查询 MySQL 范围内的日期列

另一种过滤范围内结果的方法是通过递归日期范围生成器，但它的计算成本很高。

首先，我们生成一个临时表，其中包含定义范围内的所有日期。然后我们通过 inner join 使用生成的临时表过滤目标表。

这种方法比前面的示例稍微复杂一些，但它可能与某些用例相关。

-- Using a recursive call to generate date elements
WITH RECURSIVE date_range AS (
    SELECT '2019-10-25' AS date     -- start value
   	UNION ALL
   	SELECT date + INTERVAL 1 day    -- increment by one day
   	FROM date_range
   	WHERE date < '2019-11-03')      -- stop date

SELECT id, name, date_registered
FROM registered_persons
INNER JOIN date_range
ON date_registered = date;

输出：

id	name					date_registered
2	June Popeyes			2019-10-30
3	David Craigson			2019-11-02
4	Eleanor Roosenotvelt	2019-11-03

本教程中说明的所有示例也适用于实现 datetime 或 timestamp 数据类型的列。在这种情况下，使用 DATE() 或 DATEPART() 函数在应用比较运算符之前首先提取日期组件。

这是 MySQL 中日期提取方法的官方参考。