构建测试数据

create table test_1
(
  sname varchar(10),
  score int
);
insert into test_1
select '张三',80 union all
select '李四',90 union all
select '王五',50 union all
select '陈二',70;

先看一下版本

SELECT @@VERSION 

5.6.16-log

执行如下语句

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60;

3.png

-------以上结果 在5.6版本中完全正确

我们切换到8.0版本

select @@version

8.0.25
同样构造上述的测试数据
再执行同样的语句

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60

9.png

what?? 为什么得到的sort_i 是1，2，3？

我们来看优化器改写的语句

explain
select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60
show warnings;

发现优化器 改写了我们的语句

/* select#1 */ select `t2`.`sort_i` AS `sort_i`,`t2`.`sname` AS `sname`,`t2`.`score` AS `score` 
from (/* select#2 */ select (@i1:=((@`i1`) + 1)) AS `sort_i`,`sys`.`test_1`.`sname` AS `sname`,`sys`.`test_1`.`score` AS `score` from `sys`.`test_1` where (`sys`.`test_1`.`score` > 60)) `t2`

把外层嵌套干掉了。

我先不说mysql8.0的解决办法。

再回头看看5.6版本优化器改写的后。

explain extended
select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
) t2 where score >60;

show warnings;

得到优化器改写后的语句

/* select#1 */ select `t2`.`sort_i` AS `sort_i`,`t2`.`sname` AS `sname`,`t2`.`score` AS `score`
 from (/* select#2 */ select (@i1:=((@`i1`) + 1)) AS `sort_i`,`userdb0001`.`test_1`.`sname` AS `sname`,`userdb0001`.`test_1`.`score` AS `score` from `userdb0001`.`test_1`) `t2` where (`t2`.`score` > 60)

即然8.0优化器要改写我们的语句，我们就不让它改。
改动很简单

select * from 
(
select(@i1:= @i1 + 1) as sort_i, sname,score from test_1 ,(select   @i1:= 0) t1 
limit 100
) t2 where score >60

结果也变正确了。

3.png