MySQL分组取最新2条记录的SQL在PHP执行与Workbench结果不一致如何解决

核心原因 #

你使用的SQL依赖两个MySQL用户变量@id_rank、@current_id，但没有显式初始化变量：

• MySQL Workbench运行时大概率之前执行过变量初始化操作，所以排名计算逻辑正常，返回预期的5700条结果
• PHP新建数据库连接时，两个变量初始值为NULL，@current_id = id的判断结果永远为NULL（等价于false），所以每行的id_rank都被赋值为1，全部符合<=2的筛选条件，直接返回全表近500万条数据，引发内存溢出

具体修改方案 #

直接在子查询中加入变量初始化逻辑即可，无需拆分多次SQL请求，修改后的PHP代码如下：

$subSql =
    "SELECT *, " .
    "  @id_rank := IF(@current_id = id, @id_rank + 1, 1) AS id_rank, " .
    "  @current_id := id " .
    "FROM `data` " .
    // 新增交叉连接初始化用户变量
    "CROSS JOIN (SELECT @id_rank := 0, @current_id := NULL) AS init " .
    "ORDER BY id DESC, date DESC";

$sql =
    "SELECT id, date " .
    "FROM ($subSql) ranked " .
    "WHERE id_rank <= 2 " .
    "ORDER BY id ASC, date DESC";

可选替代方案 #

你也可以在执行原有SQL前，先单独执行2条初始化语句，效果完全一致：

// 先初始化变量，此处以PDO为例，mysqli同理
$pdo->exec("SET @id_rank = 0");
$pdo->exec("SET @current_id = NULL");
// 再执行你原来的$sql即可

内容的提问来源于stack exchange，提问作者Wannabe-Coder