Laravel Eloquent获取数据时如何添加自增序号计数器

代码报错核心原因有两点：

• SET @counter = 0是独立的SQL赋值语句，不能直接写在SELECT的字段列表中，SELECT字段列表仅支持返回单列值的表达式，不支持传入独立语句。
• 语句中的分号是SQL语句分隔符，单条查询执行时碰到分号会被判定为语句截断，直接触发1064语法错误；同时Laravel默认的PDO连接默认关闭多语句执行能力，就算语法调整正确，也无法一次执行SET+SELECT两条独立语句。

根据你使用的数据库版本选择对应方案即可：

方案1：兼容低版本MariaDB/MySQL（MariaDB<10.2 / MySQL<8.0） #

用CROSS JOIN初始化用户变量，全程为单条SELECT语句，不需要单独执行SET赋值：

return JobOrder::select([
        // index是SQL保留字，必须用反引号包裹，也可以换成row_no、serial这类非保留字做别名
        DB::raw('(@counter := @counter + 1) as `index`'),
        'job_order_id',
        'job_order_code',
        'job_title',
        'job_overview',
        'posting_date',
        'visibility_order'
    ])
    ->crossJoin(DB::raw('(SELECT @counter := 0) as counter_init'))
    ->get();

注意：如果查询带排序、分页条件，必须先完成排序/过滤再做计数，否则序号会和预期排序结果不匹配，带排序的写法参考如下：

// 先构建完成排序、过滤的基础查询
$baseQuery = JobOrder::select([
        'job_order_id',
        'job_order_code',
        'job_title',
        'job_overview',
        'posting_date',
        'visibility_order'
    ])
    ->orderBy('visibility_order', 'asc'); // 替换为实际排序规则

// 基于过滤排序后的最终结果生成序号
return DB::query()
    ->select([
        DB::raw('(@counter := @counter + 1) as `index`'),
        'base.*'
    ])
    ->fromSub($baseQuery, 'base')
    ->crossJoin(DB::raw('(SELECT @counter := 0) as counter_init'))
    ->get();

方案2：高版本数据库用窗口函数（推荐） #

如果你的MariaDB版本≥10.2、MySQL版本≥8.0，直接用内置的ROW_NUMBER()窗口函数实现，不需要依赖用户变量，逻辑更稳定，不会出现序号错乱问题：

return JobOrder::select([
        DB::raw('ROW_NUMBER() OVER (ORDER BY visibility_order ASC) as `index`'),
        'job_order_id',
        'job_order_code',
        'job_title',
        'job_overview',
        'posting_date',
        'visibility_order'
    ])
    ->orderBy('visibility_order', 'asc')
    ->get();

• 不要为了跑通原代码开启PDO的多语句执行配置，该配置会大幅提升SQL注入风险，且多语句返回的结果集处理逻辑更复杂，完全没有必要。
• 不建议用index作为字段别名，它是SQL保留关键字，就算加反引号也容易在后续关联查询、导出时出现兼容问题，优先用row_no、serial_number这类语义明确的非保留字做别名。

内容的提问来源于stack exchange，提问作者Blues Clues