MySQL在WHERE子句使用row_number变量查询无返回数据如何解决

原有语句失效原因 #

MySQL中SQL子句的执行顺序为：FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT，你原有写法的问题如下：

• WHERE子句执行时，SELECT内的@row_number变量还没有完成累加，初始值为0，@row_number>10的判断始终不成立，所以没有任何数据返回
• ORDER BY @row_number是对常量排序，不会改变数据集的顺序，行号生成逻辑不稳定，必须基于业务字段指定排序规则才能保证分页结果正确

可用修正方案 #

方案1：无需返回行号的最简分页写法 #

如果不需要在结果中返回行号字段，直接使用LIMIT偏移语法即可，不需要额外计算行号：

SELECT `Ship Date`,`Order Number`
FROM input_data
-- 必须指定业务排序规则，比如按订单号/发货日期排序，保证分页结果稳定
ORDER BY `Order Number`
-- 跳过前10条，取10条记录，对应第11~20条
LIMIT 10 OFFSET 10;

该语法兼容所有MySQL版本。

方案2：兼容MySQL 5.x的用户变量写法（需要返回行号） #

如果需要在结果中返回行号，先通过子查询完成行号计算，外层再做过滤：

SET @row_number = 0; 

SELECT `Ship Date`,`Order Number`, rowid
FROM (
    SELECT 
       `Ship Date`,`Order Number`,
      (@row_number:=@row_number + 1) AS rowid
    FROM input_data
    ORDER BY `Order Number`
) AS t
WHERE rowid > 10  
LIMIT 10;

方案3：MySQL 8.0+版本的窗口函数写法（更规范） #

MySQL 8.0及以上版本支持标准SQL窗口函数，不需要手动维护变量：

SELECT `Ship Date`,`Order Number`, rowid
FROM (
    SELECT 
       `Ship Date`,`Order Number`,
       ROW_NUMBER() OVER (ORDER BY `Order Number`) AS rowid
    FROM input_data
) AS t
WHERE rowid BETWEEN 11 AND 20;

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