MySQL存储过程开发：给定年月查询近4个月数据的跨年兼容方案

核心问题分析 #

原有查询语句固定筛选Year(etimestamp) = inYear，当传入月份小于4时，需要覆盖的前一年月份会被直接过滤，导致数据缺失。同时原有写法对etimestamp字段使用Month()、Year()函数，会导致该字段的索引失效，查询性能随数据量增长明显下降。

推荐方案：时间区间查询（性能最优，兼容索引） #

推荐优先使用该方案，通过构造查询的时间起止区间直接筛选，无需对字段做函数计算，可正常使用etimestamp字段的索引。

MySQL 实现 #

-- 构造查询起止时间
SET @start_date = DATE_SUB(DATE_FORMAT(CONCAT(inYear, '-', inMonth, '-01'), '%Y-%m-%d'), INTERVAL 3 MONTH);
SET @end_date = LAST_DAY(CONCAT(inYear, '-', inMonth, '-01')) + INTERVAL 1 DAY - INTERVAL 1 SECOND;

-- 查询语句
SELECT * FROM table_name
WHERE etimestamp BETWEEN @start_date AND @end_date;

SQL Server 实现 #

-- 构造查询起止时间
DECLARE @start_date DATETIME = DATEADD(MONTH, -3, DATEFROMPARTS(inYear, inMonth, 1));
DECLARE @end_date DATETIME = DATEADD(SECOND, -1, DATEADD(MONTH, 1, DATEFROMPARTS(inYear, inMonth, 1)));

-- 查询语句
SELECT * FROM table_name
WHERE etimestamp BETWEEN @start_date AND @end_date;

备用方案：年月数值比较（无索引场景可用） #

如果没有etimestamp字段的索引，也可以通过将年月转换为数值的方式实现兼容：

SELECT * FROM table_name
WHERE (YEAR(etimestamp) * 100 + MONTH(etimestamp)) 
BETWEEN 
  CASE WHEN inMonth >=4 THEN inYear * 100 + (inMonth -3) ELSE (inYear -1)*100 + (inMonth +9) END
  AND 
  (inYear * 100 + inMonth)

以示例参数inMonth=2、inYear=2020为例，上述语句计算的最小年月数值为201911，最大为202002，刚好覆盖2019年11月到2020年2月的4个月数据，符合需求。

内容的提问来源于stack exchange，提问作者Jonnel VeXuZ Dorotan