如何查询年、月、日、分钟分字段存储的MySQL表日期范围数据

分两种场景选择对应方案，优先推荐表结构优化的方案，长期使用性能最优。

方案1：不改动原表结构（适合临时查询、数据量小于10万的场景） #

你的时间拆分字段全部为VARCHAR类型，月、日、时间值没有前导零，需要先做补零处理，再用STR_TO_DATE拼接为标准时间格式，再转timestamp即可。
注意：你表中的Minute字段为当日0点起累计的分钟数，需要换算为对应的时、分。

时间转换示例SQL #

SELECT 
  *,
  -- 生成datetime类型时间
  STR_TO_DATE(
    CONCAT(
      `Year`, '-', LPAD(`Month`, 2, '0'), '-', LPAD(`Day`, 2, '0'), ' ',
      LPAD(FLOOR(`Minute` / 60), 2, '0'), ':', LPAD(`Minute` % 60, 2, '0'), ':00'
    ),
    '%Y-%m-%d %H:%i:%s'
  ) AS event_datetime,
  -- 生成UNIX时间戳
  UNIX_TIMESTAMP(
    STR_TO_DATE(
      CONCAT(
        `Year`, '-', LPAD(`Month`, 2, '0'), '-', LPAD(`Day`, 2, '0'), ' ',
        LPAD(FLOOR(`Minute` / 60), 2, '0'), ':', LPAD(`Minute` % 60, 2, '0'), ':00'
      ),
      '%Y-%m-%d %H:%i:%s'
    )
  ) AS event_timestamp
FROM MOCK_DATA;

范围筛选示例SQL #

比如筛选2020年1月1日到2021年12月31日的数据：

SELECT * FROM MOCK_DATA
WHERE UNIX_TIMESTAMP(
  STR_TO_DATE(
    CONCAT(
      `Year`, '-', LPAD(`Month`, 2, '0'), '-', LPAD(`Day`, 2, '0'), ' ',
      LPAD(FLOOR(`Minute` / 60), 2, '0'), ':', LPAD(`Minute` % 60, 2, '0'), ':00'
    ),
    '%Y-%m-%d %H:%i:%s'
  )
) BETWEEN UNIX_TIMESTAMP('2020-01-01 00:00:00') AND UNIX_TIMESTAMP('2021-12-31 23:59:59');

该方案缺陷：因为对字段做了函数计算，无法命中索引，数据量超过10万后查询延迟会明显升高，不适合生产环境高频查询使用。

方案2：优化表结构（生产环境/大数据量场景，长期最优解） #

新增专门的TIMESTAMP类型字段存储时间，加索引后查询性能可以提升几个数量级，步骤如下：

• 新增时间字段

ALTER TABLE MOCK_DATA ADD COLUMN event_time TIMESTAMP NULL DEFAULT NULL;

• 批量回填历史数据

UPDATE MOCK_DATA
SET event_time = STR_TO_DATE(
  CONCAT(
    `Year`, '-', LPAD(`Month`, 2, '0'), '-', LPAD(`Day`, 2, '0'), ' ',
    LPAD(FLOOR(`Minute` / 60), 2, '0'), ':', LPAD(`Minute` % 60, 2, '0'), ':00'
  ),
  '%Y-%m-%d %H:%i:%s'
);

• 给时间字段加索引

CREATE INDEX idx_mock_event_time ON MOCK_DATA(event_time);

• 后续新增、修改数据时，直接写入event_time字段即可，原有拆分字段可以根据业务兼容情况逐步下线。

优化后的筛选SQL #

-- 按datetime范围筛选（自动命中索引）
SELECT * FROM MOCK_DATA 
WHERE event_time BETWEEN '2020-01-01 00:00:00' AND '2021-12-31 23:59:59';

-- 按timestamp范围筛选（和上面写法性能一致）
SELECT * FROM MOCK_DATA 
WHERE UNIX_TIMESTAMP(event_time) BETWEEN 1577836800 AND 1640966399;

注意事项：原有拆分字段都是字符串类型，回填数据前先排查脏数据（比如月份大于12、日期不合法、分钟数大于1439的记录），避免生成错误时间或NULL值。

内容的提问来源于stack exchange，提问作者Charith Jayasanka