在 MySQL 中检索日期范围内的数据
- 在 MySQL 中检索日期范围内的数据
-
使用
SELECT、WHERE和BETWEEN子句查询 MySQL 范围内的日期列 -
在 MySQL 中使用
>=和<=比较运算符查询一个范围内的日期列 - 使用递归日期范围生成器方法查询 MySQL 范围内的日期列
本教程演示如何使用这三种方法查询两个日期之间的数据库表。
在 MySQL 中检索日期范围内的数据
MySQL 通过 date 和 time 数据类型为处理日期提供了一定程度的便利，它们可以组合形成 datetime 或 timestamp。
在使用日期列时，各种比较方法可以与 SELECT 和 WHERE 子句结合使用，以有效地检索日期范围内所需的数据。
- 使用
BETWEEN子句。 - 使用其他比较运算符，如
<、>、<=和>=。 - 使用结合
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 中日期提取方法的官方参考。
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub