vlambda博客
学习文章列表

英文原版快车道：https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

你可以在语句中通过用户定义变量存储值，然后在后面的语句中引用它。这样可以让你将值从一个语句传递到另一个语句。

用户变量写作 @var_name，其中变量名 var_name 由字母、点、下划线以及美元符号组成。如果用引号括起来作为字符串或标识符，用户变量名还可以包含其他字符（比如， @'my-var', @"my-var", 或者 @`my-var`）。

用户定义变量是会话固定的，也就是说一个客户端中定义的的用户变量在别的客户端中不可见或不可用。但有一种情况例外，有访问性能模式（Performance Schema） user variables by thread 表权限的用户可以看到所有会话中的所有用户变量。对于给定客户端会话中的所有变量，在该客户端退出时自动释放。

用户变量名不区分大小写，且最大长度为64个字符。

设置用户定义变量的一种方法就是执行 SET 语句：

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

对于 SET 语句，= 或 := 都可以作为赋值操作符。

用户变量可以由一组有限的数据类型进行赋值：整型（integer），小数（ decimal），浮点数（floating-point），二进制（binary）或者 非二进制字符串（nonbinary string）或者空值（NULL value）。对小数和实数值的赋值不会保留精度或比例。其他类型的值可以进行类型转换。例如，具有时间或空间数据类型的值可以转化为二进制字符串。具有 JSON 数据类型的值可以转化为使用 utf8mb4 字符集和 utf8mb4_bin 排序规则的字符串。

如果给用户变量赋了一个非二进制字符串的值，则该变量与字符串具有相同的字符集和排序规则。

给用户变量赋十六进制或位值会被当做二进制字符串。要将所赋的十六进制或位值当做数值用在数值环境中，则可通过补0或使用 CAST(... AS UNSIGNED) 的方式进行转换：

mysql> SET @v3 = CAST(X'41' AS UNSIGNED);Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @v1, @v2, @v3;+------+------+------+| @v1 | @v2 | @v3 |+------+------+------+| A | 65 | 65 |+------+------+------+1 row in set (0.00 sec)
mysql> SET @v1 = b'1000001';Query OK, 0 rows affected (0.00 sec)
mysql> SET @v2 = b'1000001'+0;Query OK, 0 rows affected (0.00 sec)
mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @v1, @v2, @v3;+------+------+------+| @v1 | @v2 | @v3 |+------+------+------+| A | 65 | 65 |+------+------+------+1 row in set (0.00 sec)

如果在结果集中选择了用户变量的值，则它将以字符串的形式返回给客户端。如果引用了一个没有被初始化的用户变量，则它的类型为字符串，且值为空（NULL）。

用户变量可以用在大多数允许使用表达式的上下文中，目前还不包括显式要求文字值的上下文，比如 SELECT 语句的 LIMIT 子句，或者 LOAD DATA 语句的 IGNORE N LINES 子句。

也可以在除 SET 以外的语句中给用户变量赋值。（但该功能在MySQL 8.0中已被启用，在后续版本中可能删除）。如果在 SET 以外的语句中给用户变量赋值，则赋值操作符必须为 := 而不是 =，因为在 SET 以外的语句中等于符号（=）是比较运算符。

mysql> SET @t1=1, @t2=2, @t3:=4;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;+------+------+------+--------------------+| @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 |+------+------+------+--------------------+| 1 | 2 | 4 | 7 |+------+------+------+--------------------+1 row in set (0.00 sec)

一般来说，除了在 SET 语句中，切记不要在同一个语句中给用户变量赋值的同时又读取该值。比如，下面这种方式用于变量自身值增加是没问题的：

SET @a = @a + 1;

对于其他语句，比如 SELECT，你可能会得到想要的结果，但有些时候却不一定。在下面的语句中，你可能认为MySQL会先计算变量 @a 的值，然后再做赋值操作：

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

然而，调用用户变量的表达式的求值顺序却是未定义的。

在非 SET 语句中给用户变量赋值的同时又读取该值的另一个问题就是变量的默认结果类型是基于该变量在语句中首次出现时的类型。下面的示例解释了这个问题：

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

对于这个 SELECT 语句，MySQL会给客户端报告，变量 @a 在第一例中为字符串（因为它在 SET 语句中为字符串类型），然后会把之后所有调用 @a 的值都转化为字符串，哪怕在第二列中 @a 被设置为数值。

要避免这个问题，要么不在单个语句中对同一个变量既赋值又读值，要么在调用前将变量设为 0，0.0 或空字符串（''）以定义其类型。

在 SELECT 语句中，每一个选择表达式只有发送给客户端时才会被计算。这就意味着，在 HAVING, GROUP BY 或 ORDER BY 子句中，引用一个在 select 表达式列表中赋值的变量并不会得到想要的结果：

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

在 HAVING 子句中对 b 的引用其实是对 select 列表中用到变量 @aa 的表达式别名的引用。这并不会按我们所想的那样发挥作用：@aa 包含的是前面被选择行的 id 值，而不是当前行的 id 值。

用户变量旨在提供数据值，他们并不能直接用作SQL语句中的标识符或标识符的一部分，比如表名或数据库名，或者用作 SELECT 这样的保留字。即使变量加了引号也不行，看下面的示例：

mysql> SELECT c1 FROM t;+----+| c1 |+----+| 0 |+----+| 1 |+----+2 rows in set (0.00 sec)
mysql> SET @col = "c1";Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @col FROM t;+------+| @col |+------+| c1 |+------+1 row in set (0.00 sec)
mysql> SELECT `@col` FROM t;ERROR 1054 (42S22): Unknown column '@col' in 'field list'
mysql> SET @col = "`c1`";Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @col FROM t;+------+| @col |+------+| `c1` |+------+1 row in set (0.00 sec)

用户变量不能用于提供标识符的这一原则的一个例外就是，当你构造用作准备语句的字符串以便于后续执行时。这种情况下，用户变量可以用于提供语句的任何部分。看下面的示例：

mysql> SET @c = "c1";Query OK, 0 rows affected (0.00 sec)
mysql> SET @s = CONCAT("SELECT ", @c, " FROM t");Query OK, 0 rows affected (0.00 sec)
mysql> PREPARE stmt FROM @s;Query OK, 0 rows affected (0.04 sec)Statement prepared
mysql> EXECUTE stmt;+----+| c1 |+----+| 0 |+----+| 1 |+----+2 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;Query OK, 0 rows affected (0.00 sec)

PREPARE 表示预定义语句，表示将 FROM 后面的变量所表示的语句付给其前面的预定义名称（stmt），EXECUTE stmt 表示执行预定义语句，DEALLOCATE PREPARE stmt 用于释放资源。关于预定义语句（PREPARE Statements）后面会讲到。

关注我，和我一起邂逅MySQL的前世今生<~~~>