如何使用DATE_FORMAT()同时按年份和月份筛选DATETIME类型数据

错误原因 #

你改写的代码存在3个核心问题：

• DATE_FORMAT的格式参数写法错误，'%Y'-'%m'是两个字符串做减法运算，会被SQL解析为数值计算，无法输出你需要的年-月格式字符串
• 没有对传入的月份做补零处理，若传入的$month是数字8，拼接出来的对比值是2021-8，和DATE_FORMAT输出的2021-08格式不匹配，自然查不到数据
• 直接拼接变量到SQL中存在SQL注入风险，且拼接的字符串没有加引号，SQL语法本身也有问题

基于DATE_FORMAT的正确实现 #

public function getData($year, $month)
{
    // 把月份补为2位，确保和DATE_FORMAT输出的格式对齐
    $formattedMonth = str_pad($month, 2, '0', STR_PAD_LEFT);
    $targetYearMonth = $year . '-' . $formattedMonth;
    // 使用参数绑定避免SQL注入，不要直接拼接变量到SQL语句
    $sql = "SELECT * FROM foo_records WHERE DATE_FORMAT(bar_date, '%Y-%m') = ?";
    $chargeList = \DB::select($sql, [$targetYearMonth]);
    return $chargeList;
}

核心逻辑说明：DATE_FORMAT的第二个参数'%Y-%m'会把DATETIME类型的bar_date格式化为4位年份-2位月份的字符串，例如2021-08，和我们拼接的目标年月格式完全一致，就能正确匹配到对应月份的记录。

更高性能的可选方案 #

如果表数据量较大，推荐使用范围查询写法，可以利用bar_date字段上的索引，避免全表扫描计算DATE_FORMAT：

public function getData($year, $month)
{
    $formattedMonth = str_pad($month, 2, '0', STR_PAD_LEFT);
    // 当月起始时间
    $startTime = $year . '-' . $formattedMonth . '-01 00:00:00';
    // 下月起始时间，自动处理跨月、跨年逻辑，不需要手动计算当月天数
    $endTime = date('Y-m-d H:i:s', strtotime($startTime . ' +1 month'));
    $sql = "SELECT * FROM foo_records WHERE bar_date >= ? AND bar_date < ?";
    $chargeList = \DB::select($sql, [$startTime, $endTime]);
    return $chargeList;
}

内容的提问来源于stack exchange，提问作者flow Stack