[SQL] MySQL用户自定义变量

MySQL用户自定义变量详解

你可以利用SQL语句将值存储在用户自定义变量中，然后再利用另一条SQL语句来查询用户自定义变量。这样以来，可以再不同的SQL间传递值。

用户自定义变量的声明方法形如：@var_name，其中变量名称由字母、数字、“.”、“_” 和 “$” 组成。当然，在以字符串或者标识符引用时也可以包含其他字符（例如：@‘my-var’，@“my-var”，或者@`my-var`）。

用户自定义变量是会话级别的变量。其变量的作用域仅限于声明其的客户端链接。当这个客户端断开时，其所有的会话变量将会被释放。

用户自定义变量是不区分大小写的。

使用 SET 语句来声明用户自定义变量：

SET @var_name = expr [, @var_name = expr] ...

在使用 SET 设置变量时，可以使用 “=” 或者 “:=” 操作符进行赋值。

当然，除了 SET 语句还有其他赋值的方式。比如下面这个例子，但是赋值操作符只能使用 “:=”。因为 “=” 操作符将会被认为是比较操作符。

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+

用户变量的类型仅限于：整形、浮点型、二进制与非二进制串和 NULL。在赋值浮点数时，系统不会保留精度。其他类型的值将会被转成相应的上述类型。比如：一个包含时间或者空间数据类型（temporal or spatial data type）的值将会转换成一个二进制串。

如果用户自定义变量的值以结果集形式返回，系统会将其转换成字符串形式。

如果查询一个没有初始化的变量，将会以字符串类型返回 NULL。

---

不要在同一个非SET语句中同时赋值并使用同一个用户自定义变量

用户自定义变量可以用于很多上下文中。但是目前并不包括那些显式使用常量的表达式中，比如 SELECT 中的 LIMIT 子句，或者 LOAD DATA 中的 IGNORE N LINES 的字句中。

通常来说，除了在 SET 语句中，不要再同一个 SQL 语句中同时赋值并使用同一个用户自定义变量。举个变量自增的例子，下面的是没问题的：

SET @a = @a + 1;

对于其他语句，比如 SELECT，也许会得到期望的效果，但这真心不靠谱。比如下面的语句，也许你自然地会认为 MySQL 会先执行 @a 的值，然后再进行赋值操作：

SELECT @a, @a:=@a+1, ...;

然而，用户自定义变量表达式的计算顺序还没有定义呢。

除此之外，还有另一个问题。变量的默认返回类型由语句开始时的类型决定的，正如下面的例子：

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

上述的 SELECT 语句中，MySQL 会报告给客户端第一列的字段类型为字符串，同时将所有对 @a 变量的使用均转换为字符串处理，尽管在 SELECT 语句中将 @a 变量设置为数字类型。在 SELECT 语句执行后，@a 变量才会在下一个语句中识别为数字类型。

为了避免上述问题的发生，要么不在同一个语句中同时赋值并使用变量，要么在使用之前，将变量设置为 0，0.0，或者 ‘’，以确定它的数据类型。

---

变量的值是在SQL发送到客户端后才计算的

在 SELECT 语句中，在每一个 select 表达式被发送给客户端后，才会进行计算。这就意味着，在形如 HAVING，GROUP BY 和 ORDER BY 只句中有使用在当前 select 表达式定义的变量的情况下，该语句将不会得到如期的效果。

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

上述在 HAVING 只句中使用了在当前的 select 列表中定义的别名 b，其使用了变量@aa。这条语句并不会得到如期的效果：@aa 变量为上一次 SQL 语句执行的结果集中的 ID 值，并非当前的。

---

译文来源：深入MySQL用户自定义变量：使用详解及其使用场景案例
官方文档：9.4 User-Defined Variables