自定义变量执行顺序相关
# MySQL自定义变量执行顺序
高性能MySQL 248页
关于书中 MySQL自定义变量 确定取值顺序的一些实验和猜测.
并不能保证正确.
```sql
实验数据,test表 a是主键,b字段没有任何索引.
CREATE TABLE test (
a int(11) NOT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (a)
) ENGINE=InnoDB;
INSERT INTO test VALUES (1,100),(2,20),(3,5),(4,40),(5,50);
commit;
```
c
## 第一个例子
查看如下SQL,按道理应该只有一条记录,但是却返回了两条记录.
```sql
set @rownum:=0;
select a,b,@rownum:=@rownum+1 as cnt
from test
where @rownum<=1;
```
这是因为自定义变量在SQL执行的不同阶段被查询或运算.
以上面的SQL为例
第一步先获取一条数据,此时@rownum为0,通过了where条件,在select语句自增后等于1
第二步获取第二条数据,此时@rownum为1,通过了where条件,在select语句自增后等于2
第三步获取数据,由于@rownum等于2,不能通过where条件,SQL结束.
> **解释：** 在这里发现它的执行顺序并不是where执行完毕生成一个完整的虚拟表给select然后结束where语句，而是类似从where的表中提取一条数据给select，select执行完毕后再提取一条数据给select直至提取完毕。
>
>**我的想法是：** 此处可能涉及到某种优化，优化器检测到where语句使用到自定义变量，而其赋值却是在select语句中，因此进行优化，一条一条执行。
## 第二个例子
如果上面的SQL增加排序,居然返回了全部的数据
```sql
set @rownum:=0;
select a,b,@rownum:=@rownum+1 as cnt
from test
where @rownum<=1
order by b;
```
它的执行计划如下：
这是因为order by引入了文件排序,而where条件是在文件排序操作之前取值的.
也就是说,先执行where条件,这时@rownum为0,所有的记录都符合条件.
然后排序,并执行select语句对@rownum自增.
**解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段（见例四）**
> **解释**：此处违背了SQL语句执行顺序的规律，他这里是**先执行了 order by ，再执行 select**
>
>**我的想法**：查了一些博客，均没有讲清楚，其中一个博客提到mysql的官方文档也未作说明，那就将其当作特例吧！然后，这里的where语句应该是检测到接下来需要排序，而排序需要所有的数据而不能像先前那样一条一条取出给select操作（这里order by在select之前执行）因此直接按照常规将所有满足条件数据生成一个虚拟表给 order by。
## 第三个例子
有意思的是,如果这时对b字段创建一个索引,则返回的结果截然不同.
此时的执行计划
由于b字段有索引,
先根据索引获取第一条记录,此时@rownum为0,通过where条件,然后在select语句对@rownum自增,
然后根据索引获取第二条数据,此时@rownum为1,通过where条件,然后在select语句自增,这时@rownum已经为2
由于@rownum已经为2,不能通过where条件,SQL结束.
> **解释**：这里因为b字段已经有了索引，就代表其已经排好序了，就不需要再排序了，直接按照b字段的顺序取数据，就又回到了例1.
## 第四个例子
```sql
删除之前例子对字段b创建的索引
mysql> drop index inx_1 on test;
查看如下SQL
set @rownum:=0;
select a,b,@rownum as cnt
from test
where (@rownum:=@rownum+1)<=2;
```
这个结果应该是符合预期的
因为**自增@rownum和where条件判断都在SQL执行的同一阶段.**
## 第五个例子
```sql
set @rownum:=0;
select a,b,@rownum as cnt
from test
where (@rownum:=@rownum+1)<=1 order by b desc;
```
它的执行计划,又见文件排序.
首先,它获取第一条记录,自增@rownum然后判断where条件,a=1的记录通过.之后所有的记录都不能通过where条件.
虽然后续的记录没有通过,但是@rownum也会自增.转入order by 阶段之前,@rownum已经等于5了.
然后进入order by 文件排序阶段
最后进入select语句,显示@rownum为5.
**在where中不过滤的结果是这样的。**
如果再次创建字段b的索引,由于没有文件排序的过程,where条件之后,直接进入select语句阶段,所以结果如下
## 第六个例子
再次删除字段b上的索引.查看如下SQL语句
```sql
drop index inx_1 on test;
set @rownum:=0;
select a,b,@rownum as cnt
from test
where @rownum<=1
order by b desc,least(0,@rownum:=@rownum+1);
```
>在上面的最后一个例子中，我们引入了一个**新的技巧**：我们将赋值语句放到LEAST( )函数中，这样就可以在完全不改变排序顺序的时候完成赋值操作 (**在上面例子中，LEAST( )函数总是返回0**)。这个技巧在不希望对子句的执行结果有影响却又要完成变量赋值的时候很有用。这个例子中，无须在返回值中新增额外列。这样的函数还有GREATEST( )、LENGHT( )、ISNULL()、 NULLIFL( )、 IF( ) 和COALESCE( )，可以单独使用也可以组合使用。例如，COALESCE( ) 可以在一-组参数中取第-一个已经被定义的变量。
这个结果应该不符合预期
先获取一个记录,通过where条件(@rownum=0)，进入order by 语句?
再获取一个记录,通过where条件(@rownum=1)，进入order by 语句?
然后不能通过where条件,SQL结束?
不确定.
**但是如果b字段有索引.**
因为MySQL只有升序索引,所以他先从最小值读取,先通过where条件，然后进入order by语句
所以,order by .. least 的方式,**只能用于升序的情况**.
---
参考：[MySQL自定义变量执行顺序](http://blog.itpub.net/29254281/viewspace-1415038/)