MySQL 5.7用JSON变量实现分组排名在Laravel 5.4中异常求助

核心问题原因 #

• MySQL会话变量（如@row_number）是线程绑定的，Laravel的数据库连接池或单次查询的会话上下文，和TablePlus的独立会话不同，导致变量状态未正确重置。
• 变量初始化时机不对、更新顺序逻辑有问题，或是JSON变量赋值延迟，引发计数停滞、extracted_counter为null的情况。

修复方案 #

1. 强制重置会话变量，确保初始状态一致 #

在执行分组计数主查询前，单独执行变量初始化语句，清除会话残留的旧值干扰：

// 先重置所有用到的会话变量
DB::statement('SET @row_number = 0, @current_group = NULL');

// 再执行分组计数查询
$result = DB::select('
    SELECT group_id, counter
    FROM (
        SELECT 
            group_id,
            @row_number := IF(@current_group = group_id, @row_number + 1, 1) AS counter,
            @current_group := group_id
        FROM your_table
        ORDER BY group_id, id -- 必须按分组字段+排序字段先排序，保证计数逻辑正确
    ) t
    WHERE t.counter < 250
');

2. 移除JSON变量依赖，简化计数逻辑 #

既然JSON变量出现null问题，直接用计算出的counter字段做筛选，避免不必要的JSON转换：

-- 子查询内直接计算分组计数，外层直接筛选
SELECT group_id, counter
FROM (
    SELECT 
        group_id,
        @row_number := IF(@current_group = group_id, @row_number + 1, 1) AS counter,
        @current_group := group_id
    FROM your_table
    ORDER BY group_id, created_at
) t
WHERE t.counter < 250

3. 调整查询执行方式，避免连接池干扰 #

如果是连接池复用会话导致的变量残留，可临时断开并重新连接：

// 断开当前连接，强制创建新会话
DB::disconnect();
DB::connection()->reconnect();

// 再执行变量初始化和主查询

4. 确保子查询排序优先级 #

分组计数的核心是先按分组字段排序，否则分组内的计数顺序会混乱，导致计数停滞在某个数值：

-- 子查询必须先ORDER BY分组字段，再执行计数逻辑
SELECT 
    group_id,
    @row_number := IF(@current_group = group_id, @row_number + 1, 1) AS counter,
    @current_group := group_id
FROM your_table
ORDER BY group_id, id -- 排序字段根据业务需求调整

关键注意事项 #

• Laravel的DB::select()是单次执行查询，若把变量初始化和主查询写在同一条语句中，可能因执行顺序问题导致初始化不生效，必须分开执行初始化语句。
• 不要依赖会话变量的跨查询状态，每次执行分组计数前都显式重置变量。

内容的提问来源于stack exchange，提问作者Nikita Volchok