本文介绍如何使用 sql 提取数据库中“历史上的今天”类内容——即与当前日期月份、日期相同但年份更早的所有文章记录，并提供可直接集成的 codeigniter 风格 php 实现。
本文介绍如何使用 sql 提取数据库中“历史上的今天”类内容——即与当前日期月份、日期相同但年份更早的所有文章记录，并提供可直接集成的 codeigniter 风格 php 实现。
在内容运营或时间轴类功能开发中，常需实现“历史上的今天”模块：展示往年同月同日发布的文章，而非仅限于当前年份。这要求 SQL 查询精准匹配 month 和 day，同时严格排除本年度数据。
核心思路是按日期组成部分（年、月、日）分别提取并比对，而非依赖日期范围（如 BETWEEN），因为跨年场景下范围查询易出错且低效。PostgreSQL/MySQL 8.0+/Oracle 等主流数据库均支持 EXTRACT() 函数，可安全提取时间字段的结构化成分。
以下是优化后的完整方法：
public function get_on_this_day_events($lang_id, $limit)
{
$sql = "SELECT * FROM posts
WHERE
EXTRACT(DAY FROM posts.created_at) = EXTRACT(DAY FROM CURRENT_TIMESTAMP)
AND EXTRACT(MONTH FROM posts.created_at) = EXTRACT(MONTH FROM CURRENT_TIMESTAMP)
AND EXTRACT(YEAR FROM posts.created_at) < EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
AND posts.lang_id = ?
ORDER BY posts.created_at DESC
LIMIT ?";
$query = $this->db->query($sql, [
clean_number($lang_id),
clean_number($limit)
]);
return $query->result();
}✅ 关键改进说明：
- 补充了 lang_id 条件（原答案遗漏），确保多语言内容正确过滤；
- 将 ORDER BY 和 LIMIT 移至主查询，避免子查询嵌套导致的性能与语法风险（原问题中错误地将 WHERE 写在子查询括号内）；
- 使用 CURRENT_TIMESTAMP 替代 CURRENT_DATE，确保时区一致性（尤其当 created_at 含时间部分时）；
- 显式添加
⚠️ 注意事项：
- 若数据库为 MySQL 5.7 或更低版本，需改用 DAYOFMONTH()、MONTH()、YEAR() 函数替代 EXTRACT；
- 确保 posts.created_at 字段为 DATETIME 或 TIMESTAMP 类型，DATE 类型亦可，但需注意索引有效性；
- 建议为 (created_at, lang_id) 创建复合索引以加速查询，例如：
CREATE INDEX idx_posts_on_this_day ON posts (YEAR(created_at), MONTH(created_at), DAY(created_at), lang_id);
（注：部分数据库不支持函数索引，此时可考虑生成 post_month_day 计算列并建索引）
该方案逻辑严谨、可读性强，适用于新闻聚合、周年回顾、内容重推等典型业务场景。
