Laravel 5.7中DB::Raw绑定参数失效及报错问题求助

问题根源分析 #

你遇到的两个问题都是因为对Laravel的参数绑定机制理解有误：

1. 第一种写法返回null的原因：
   你在STR_TO_DATE('?', '%Y-%m-%d')里给?加了单引号，这会让MySQL把'?'当成一个字面量字符串，而不是参数占位符。数据库会尝试把字符串"?"转换成日期，结果自然是null，导致子查询没有匹配的记录，最终返回null。

2. 第二种写法出现HY093错误的原因：
   Laravel的DB::raw()方法本身不支持直接传递参数数组——你后面跟的[ $dates['from'], $dates['to'] ]会被完全忽略。而你的整个查询中，除了三个子查询里的6个?，还有exists子句里的2个?，总共8个占位符，但你没有给整个查询构造器传递足够且顺序正确的参数，导致数据库提示参数数量不匹配。

正确的解决方案 #

下面提供两种可靠的参数绑定方式，既避免SQL注入，又能正常运行：

方式一：按顺序绑定所有参数（适合简单场景）

把所有?占位符的参数按顺序传递给整个查询构造器，用setBindings()统一设置：

$from = $dates['from'];
$to = $dates['to'];

$drivers = DB::table('drivers')
    ->select([
        'id',
        'name',
        'surname',
        'phone',
        'driver_status',
        'updated_at',
        'photo',
        // 工资计算子查询
        DB::raw("(select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(?, '%Y-%m-%d') and STR_TO_DATE(?, '%Y-%m-%d')) as salary"),
        // 工作时长子查询
        DB::raw("(select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 )) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(?, '%Y-%m-%d') and STR_TO_DATE(?, '%Y-%m-%d')) as worked_hours"),
        // 测试用子查询
        DB::raw("(select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(?, '%Y-%m-%d') and STR_TO_DATE(?, '%Y-%m-%d')) as test")
    ])
    ->whereExists(function ($query) use ($from, $to) {
        $query->select(DB::raw(1))
              ->from('assignments')
              ->whereRaw('drivers.id = assignments.driver_id')
              ->whereRaw('driving_day between ? and ?', [$from, $to]);
    })
    // 按所有?的顺序传递参数：3个子查询各2个，exists里2个，共8个
    ->setBindings([$from, $to, $from, $to, $from, $to, $from, $to])
    ->paginate(15);

方式二：使用命名占位符（更清晰，避免顺序错误）

用命名占位符:from_date和:to_date代替?，这样不用纠结参数顺序，可读性更强：

$from = $dates['from'];
$to = $dates['to'];

$drivers = DB::table('drivers')
    ->select([
        'id',
        'name',
        'surname',
        'phone',
        'driver_status',
        'updated_at',
        'photo',
        DB::raw("(select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(:from_date, '%Y-%m-%d') and STR_TO_DATE(:to_date, '%Y-%m-%d')) as salary"),
        DB::raw("(select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 )) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(:from_date, '%Y-%m-%d') and STR_TO_DATE(:to_date, '%Y-%m-%d')) as worked_hours"),
        DB::raw("(select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10) from assignments where assignments.driver_id = drivers.id and assignments.driving_day between STR_TO_DATE(:from_date, '%Y-%m-%d') and STR_TO_DATE(:to_date, '%Y-%m-%d')) as test")
    ])
    ->whereExists(function ($query) use ($from, $to) {
        $query->select(DB::raw(1))
              ->from('assignments')
              ->whereRaw('drivers.id = assignments.driver_id')
              ->whereRaw('driving_day between :from_date and :to_date', compact('from', 'to'));
    })
    // 给select子句绑定命名参数
    ->addBinding(['from_date' => $from, 'to_date' => $to], 'select')
    ->paginate(15);

额外优化建议 #

你的三个子查询逻辑重复度很高，可以考虑用JOIN简化查询，减少数据库的重复计算压力：

$drivers = DB::table('drivers')
    ->join(DB::raw("(select driver_id, 
                        10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ) * (drivers.salary_per_hour / 3600) / 10) as salary,
                        SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 )) as worked_hours
                     from assignments 
                     where driving_day between STR_TO_DATE(:from_date, '%Y-%m-%d') and STR_TO_DATE(:to_date, '%Y-%m-%d')
                     group by driver_id) as assignment_summary"), function ($join) {
        $join->on('drivers.id', '=', 'assignment_summary.driver_id');
    })
    ->select('drivers.*', 'assignment_summary.salary', 'assignment_summary.worked_hours')
    ->addBinding(['from_date' => $dates['from'], 'to_date' => $dates['to']])
    ->paginate(15);

这样只需要一次子查询计算所有司机的工资和时长，性能会更好。

内容的提问来源于stack exchange，提问作者Vardan Nersesyan