【MySQL】用户自定义变量

用户自定义变量是一个用来存储内容的临时容器，在连接MySQL的整个过程中都存在。可以SET和SELECT语句来定义。

SET @one := 1；
SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor);
SET @last_week := CURRENT_DATE-INTERNAL 1 WEEK;

MySQL 自定义变量的局限性：

• 使用自定义变量的查询，无法使用查询缓存。
• 不能再使用常量或者标识符的地方使用自定义变量，例如表名、列名和LIMIT子句中。
• 用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信。
• 如果使用连接池或持久连接，自定义变量会导致你的代码与交互隔离，这出现的时候可能是代码的 bug 或连接池的 bug，但是是可能发生的。
• 在 MySQL 5.0以前的版本中是大小写敏感的，因此要注意（在 MySQL 5.0以后已经不区分大小写了）。
• 不能显式地声明自定义变量的类型。确定未定义变量的具体类型的时机在不同MySQL版本中也可能不一样。如果你希望变量是整数类型，那么最好在初始化的时候就赋值为0，如果希望是浮点型则赋值为0.0，如果希望是字符串则赋值为’’，用户自定义变量的类型在赋值的时候会改变。MySQL的用户自定义变量是一个动态类型。
• MySQL优化器在某项些场景下可能会将这些变量优化掉，这可能导致代码不按预想的方式运行。
• 赋值的顺序和赋值的时间点并不总是固定的，这依赖于优化器的决定，实际情况可能很让人困惑。
• 赋值符号:=的优先级非常低，所以需要注意，赋值表达式应该使用明确的括号。
• 使用未定义变量不会产生任何语法错误，如果没有意识到这一点，非常容易犯错。

用户自定义变量的用法:

• 查询运行时计算总数和平均值。
• 模拟GROUP BY语句中的函数FIRST()和LAST()。
• 对大量数据做一些数据计算。
• 计算一个大表的MD5散列值。
• 编写一个样本处理函数，当样本中的数值超过某个边界值的时候将其变为0。
• 模拟读/写游标。
• 在SHOW语句的WHERE子句中加入变量值。

案例：

1、优化排名

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS rownum
FROM actor order by actor_id LIMIT 3;
    +----------+--------+
    | actor_id | rownum |
    +----------+--------+
    |        1 |      1 |
    |        2 |      2 |
    |        3 |      3 |
    +----------+--------+

mysql> SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0;
mysql> SELECT actor_id, COUNT(*) as cnt
    -> FROM film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10;
    +----------+-----+
    | actor_id | cnt |
    +----------+-----+
    |      107 |  42 |
    |      102 |  41 |
    |      198 |  40 |
    |      181 |  39 |
    |       23 |  37 |
    |       81 |  36 |
    |       37 |  35 |
    |      106 |  35 |
    |       60 |  35 |
    |       13 |  35 |
    +----------+-----+

mysql> SELECT actor_id,
    -> @curr_cnt := COUNT(*) AS cnt,
    -> @rank     := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank,
    -> @prev_cnt := @curr_cnt AS dummy
    -> FROM film_actor
    -> GROUP BY actor_id
    -> ORDER BY cnt DESC
    -> LIMIT 10;
    +----------+-----+------+-------+
    | actor_id | cnt | rank | dummy |
    +----------+-----+------+-------+
    |      107 |  42 |    0 |     0 |
    |      102 |  41 |    0 |     0 |
    |      198 |  40 |    0 |     0 |
    |      181 |  39 |    0 |     0 |
    |       23 |  37 |    0 |     0 |
    |       81 |  36 |    0 |     0 |
    |      106 |  35 |    0 |     0 |
    |       60 |  35 |    0 |     0 |
    |       13 |  35 |    0 |     0 |
    |       37 |  35 |    0 |     0 |
    +----------+-----+------+-------+

2、避免重复查询刚刚更新的数据

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1;
SELECT lastUpdated FROM t1 WHERE id = 1;

UPDATE t1 SET lastUpdated = NOW() WHERE id = 1 AND @now := NOW();
SELECT @now;

实际项目的案例,由于User_A关联的ORGAN_A可以为空，所以每条数据查询获取要提前将变量@lxDm置空，避免上一条数据影响。

SELECT (case when LEAST('1',@lxDm:='') IS NOT NULL THEN '2021' ELSE '2021' END) ND,
(SELECT @lxDm:=lxDm FROM ORGAN_A WHERE id = organ_id LIMIT 1) as lxDm,
(SELECT dict_name FROM G_DICT WHERE dict_id = @lxDm) as lxDmStr
FROM User_A 
WHERE ....

3、统计更新和插入的数量

INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1)
ON DUPLICATE KEY UPDATE
    c1 = VALUES(c1) + (0 * (@x := @x + 1));

当每次由于冲突导致更新时对变量@x自增一次，然后表达式乘以0让其不影响更新的内容，另外，MySQL的协议会返回被更改的总行数，所以不需要单独统计。

4、确定取值的顺序

使用用户自定义变量的一个最常见的问题就是没有注意到在赋值和读取变量的时候可能是在查询的不同阶段。例如，在SELECT子句中进行赋值然后再WHERE子句中读取变量，则可能变量取值并不如你所想：

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
    -> FROM actor
    -> WHERE @rownum <= 1;
+----------+------+
| actor_id | cnt  |
+----------+------+
|       58 |    1 |
|       92 |    2 |
+----------+------+

因为WHERE和SELECT是在查询执行的不同阶段被执行的。如果在查询中再加入ORDER BY的话，结果可能会更不同；

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum := @rownum + 1 AS cnt
    -> FROM actor
    -> WHERE @rownum <= 1
    -> ORDER BY first_name;

这是因为ORDER BY 引入了文件排序，而WHERE条件是在文件排序操作之前取值的，所以这条查询会返回表中的全部记录。解决这个问题的办法是让变量的赋值和取值发生在执行查询的同一阶段：

mysql> SET @rownum := 0;
mysql> SELECT actor_id, @rownum AS rownum
    -> FROM actor
    -> WHERE (@rownum := @rownum + 1) <= 1;
+----------+--------+
| actor_id | rownum |
+----------+--------+
|       58 |      1 |
+----------+--------+

在不改变排序的情况下赋值。

mysql> SET @rownum := 0;
mysql> SELECT actor_id,first_name,@rownum AS rownum
    -> FROM actor
    -> WHERE @rownum <= 1
    -> ORDER BY first_name,LEAST(0,@rownum := @rownum + 1);

5、编写偷懒的UNION

假设需要编写一个UNION查询，其第一个子查询作为分支条件先执行，如果找到了匹配的行，则跳过第二个分支。例如先在一个频繁访问的表查找热数据，找不到再去另外一个较少访问的表查找冷数据。

SELECT id FROM users WHERE id = 123;
UNION ALL
SELECT id FROM users_archived WHERE id = 123;

上面的查询可以工作，但是无论第一个表找没找到，都会在第二个表再找一次，如果使用变量的话可以很好地规避这个问题。

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl
FROM users WHERE id = 1
UNION ALL
    SELECT id, 'users_archived'
    FROM users_archived WHERE id = 1 AND @found IS NULL
UNION ALL   
    SELECT 1, 'reset' FROM DUAL WHERE (@found := NULL) IS NOT NULL;