先简单介绍下mysql自定义变量的使用方法
注：文章所用的表如果没有带创建语句的都是mysql官方提供的数据库（sakila库）和表，可以上官网上去下载
自定义变量可以是一个常量、一条SQL语句、一个表达式
mysql> set @one :=1;
Query OK, 0 rows affected (0.01 sec)
mysql> set @min_actor :=(select min(actor_id) from actor);
Query OK, 0 rows affected (0.10 sec)
mysql> set @last_week :=current_date-interval 1 week;
Query OK, 0 rows affected (0.07 sec)
mysql> select @one;
+------+
| @one |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> select @min_actor
-> ;
+------------+
| @min_actor |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select @last_week
-> ;
+------------+
| @last_week |
+------------+
| 2020-12-06 |
+------------+
1 row in set (0.00 sec)自定义变量使用案例和注意事项
1、优化排名
1）在给一个变量赋值的同时使用这个变量
mysql> select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;
+----------+--------+
| actor_id | rownum |
+----------+--------+
| 58 | 1 |
| 92 | 2 |
| 182 | 3 |
| 118 | 4 |
| 145 | 5 |
| 194 | 6 |
| 76 | 7 |
| 112 | 8 |
| 67 | 9 |
| 190 | 10 |
+----------+--------+
10 rows in set, 1 warning (0.00 sec)2）查询获取演过最多电影的前十名演员，然后根据电影次数做一个排名
mysql> set @rownum=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * ,@rownum:=@rownum+1 as rownum from (select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10) t;
+----------+-----+--------+
| actor_id | cnt | rownum |
+----------+-----+--------+
| 107 | 42 | 1 |
| 102 | 41 | 2 |
| 198 | 40 | 3 |
| 181 | 39 | 4 |
| 23 | 37 | 5 |
| 81 | 36 | 6 |
| 60 | 35 | 7 |
| 158 | 35 | 8 |
| 106 | 35 | 9 |
| 13 | 35 | 10 |
+----------+-----+--------+
10 rows in set, 1 warning (0.00 sec)2、避免重新查询刚刚更新的变量
当需要高效的更新一条记录的时间戳，同时希望查询当前记录中存放的时间戳是什么
例子：
准备数据
mysql> create table t1(id int,t_date datetime);
Query OK, 0 rows affected (0.49 sec)
mysql> insert into t1(id,t_date) values(1,now());
Query OK, 1 row affected (0.05 sec)
mysql> select * from t1;
+------+---------------------+
| id | t_date |
+------+---------------------+
| 1 | 2020-12-13 22:32:11 |
+------+---------------------+更新+查询
mysql> update t1 set t_date=now() where id =1;
Query OK, 1 row affected (0.14 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select t_date from t1 where id =1;
+---------------------+
| t_date |
+---------------------+
| 2020-12-13 22:32:35 |
+---------------------+
1 row in set (0.00 sec)使用自定义变量优化，效率会高很多
mysql> update t1 set t_date = now() where id = 1 and @now:=now();
Query OK, 1 row affected, 1 warning (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> select @now;
+---------------------+
| @now |
+---------------------+
| 2020-12-13 22:33:16 |
+---------------------+
1 row in set (0.00 sec)3、注意--确定取值顺序
在赋值和读取变量的时候可能是在查询的不同阶段
---因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期
mysql> set @rownum:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1;
+----------+------+
| actor_id | cnt |
+----------+------+
| 58 | 1 |
| 92 | 2 |
+----------+------+
2 rows in set, 1 warning (0.00 sec)--当引入了order by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的
mysql> set @rownum:=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name
-> ;
+----------+------+
| actor_id | cnt |
+----------+------+
| 71 | 1 |
| 132 | 2 |
| 165 | 3 |
| 173 | 4 |
| 125 | 5 |
| 146 | 6 |
| 29 | 7 |
| 65 | 8 |
...........
...........
...........--解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：
mysql> select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;
+----------+------+
| actor_id | cnt |
+----------+------+
| 58 | 1 |
+----------+------+
1 row in set, 1 warning (0.00 sec)自定义变量的限制：
1、无法查询缓存
2、不能在使用常量或者标识符的地方使用自定义变量
3、用户自定义变量只在一个连接周期中有效，所以不能用它们来做连接间的通讯
4、不能显示的申明自定义变量类型（不定义也能查，不会报错，容易导致结果不符合预期）
5、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想方式运行
6、赋值符号：=的优先级非常低，在使用赋值表达式的时候应该明确的使用括号
7、使用未定义变量不会产生任何的语法错误
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
