在 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