如何在仅提供年、月、日独立字段的情况下按日期范围筛选行

核心逻辑 #

由于你的表已经在year、month、day字段上建立了联合索引/聚簇索引，我们只要避免对这三个字段做函数计算、字符串拼接等操作，就能完全命中索引，实现秒级查询。

示例查询代码 #

以下是查询2020-12-31到2021-02-01区间所有数据的写法：

SELECT id, year, month, day
FROM your_table_name
WHERE
-- 年份在起止年份中间的所有记录直接命中
(year > 2020 AND year < 2021)
-- 匹配起始年的月日下限
OR (year = 2020 AND (month > 12 OR (month = 12 AND day >= 31)))
-- 匹配结束年的月日上限
OR (year = 2021 AND (month < 2 OR (month = 2 AND day <= 1)));

通用参数模板 #

你可以按需修改起止日期参数，适配任意查询区间：

-- 定义查询的起止日期参数
SET @start_y = 2020, @start_m = 11, @start_d = 1;
SET @end_y = 2021, @end_m = 2, @end_d = 12;

SELECT * FROM your_table_name
WHERE
(year > @start_y AND year < @end_y)
OR (year = @start_y AND (month > @start_m OR (month = @start_m AND day >= @start_d)))
OR (year = @end_y AND (month < @end_m OR (month = @end_m AND day <= @end_d)));

特别注意：禁止使用CONCAT(year,month,day)、日期转换函数等方式拼接字段做比较，这类操作会导致索引失效，触发全表扫描，查询性能会跌到和用timestamp字段查询一致的数小时级别。

内容的提问来源于stack exchange，提问作者Joe King