9.4 用户定义的变量
您可以在一个语句中将值存储在用户定义的变量中,稍后在另一条语句中引用它。这使您可以将值从一个语句传递到另一个语句。
用户变量写为@var_name
,其中变量名* var_name
*由字母数字字符.
,_
和$
组成。如果您将用户变量名称作为字符串或标识符引号,则它可以包含其他字符(例如,@'my-var'
,@"my-var"
或``@`my-var```)。
用户定义的变量是特定于会话的。一个 Client 端定义的用户变量不能被其他 Client 端看到或使用。 (exception:有权访问“性能模式user_variables_by_thread”table 的用户可以查看所有会话的所有用户变量。)当给定的 Client 端会话退出时,该 Client 端会话的所有变量将自动释放。
用户变量名称不区分大小写。名称的最大长度为 64 个字符。
设置用户定义变量的一种方法是发出SET语句:
SET @var_name = expr [, @var_name = expr] ...
可以从一组有限的数据类型中为用户变量分配一个值:整数,十进制,浮点数,二进制或非二进制字符串或NULL
值。分配小数和实数值不会保留数值的精度或小数位数。除允许的类型之一以外的其他类型的值将转换为允许的类型。例如,将具有时间或空间数据类型的值转换为二进制字符串。数据类型为JSON的值将转换为字符集为utf8mb4
且排序规则为utf8mb4_bin
的字符串。
如果为用户变量分配了非二进制(字符)字符串值,则该变量具有与字符串相同的字符集和排序规则。用户变量的强制性是隐式的。 (这与 table 列值具有相同的强制性.)
分配给用户变量的十六进制或位值被视为二进制字符串。要将十六进制或位值作为数字分配给用户变量,请在数字上下文中使用它。例如,添加 0 或使用投射(...为未签名):
mysql> SET @v1 = X'41';
mysql> SET @v2 = X'41'+0;
mysql> SET @v3 = CAST(X'41' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| A | 65 | 65 |
+------+------+------+
mysql> SET @v1 = b'1000001';
mysql> SET @v2 = b'1000001'+0;
mysql> SET @v3 = CAST(b'1000001' AS UNSIGNED);
mysql> SELECT @v1, @v2, @v3;
+------+------+------+
| @v1 | @v2 | @v3 |
+------+------+------+
| A | 65 | 65 |
+------+------+------+
如果在结果集中选择了用户变量的值,则将其作为字符串返回给 Client 端。
如果您引用的变量尚未初始化,则其值为NULL
和字符串类型。
用户变量可以在允许使用 table 达式的大多数上下文中使用。当前,这不包括明确需要 Literals 值的上下文,例如SELECT语句的LIMIT
子句或LOAD DATA语句的IGNORE N LINES
子句。
还可以在SET以外的语句中为用户变量分配值。 (此功能在 MySQL 8.0 中已弃用,并在以后的版本中删除.)以这种方式进行赋值时,赋值运算符必须为:=而不是=,因为在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 |
+------+------+------+--------------------+
通常,除了在SET语句中,永远不要为用户变量分配值,并且不要在同一条语句中读取该值。例如,要增加变量,可以:
SET @a = @a + 1;
对于其他语句,例如SELECT,您可能会得到期望的结果,但这不能保证。在下面的语句中,您可能认为 MySQL 首先会评估@a
,然后再进行赋值:
SELECT @a, @a:=@a+1, ...;
但是,涉及用户变量的 table 达式的求值 Sequences 是不确定的。
将值分配给变量并在同一非SET语句中读取值的另一个问题是,变量的默认结果类型基于语句开头的类型。以下示例说明了这一点:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
对于此SELECT语句,即使@a 设置为第二行的数字,MySQL 也会向 Client 端报告第一列是字符串,并将对@a
的所有访问转换为字符串。 SELECT语句执行后,@a
被视为下一条语句的数字。
为避免此行为出现问题,请不要在单个语句中给同一变量赋值并读取同一变量的值,或者在使用变量之前将其设置为0
,0.0
或''
以定义其类型。
在SELECT语句中,仅在将每个选择 table 达式发送给 Client 端时对其求值。这意味着在HAVING
,GROUP BY
或ORDER BY
子句中,引用在选择 table 达式列 table 中分配了值的变量并不能按预期工作:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
HAVING
子句中对b
的引用是指使用@aa
的选择列 table 中 table 达式的别名。这不能按预期方式工作:@aa
包含来自上一个选定行而不是来自当前行的id
的值。
用户变量旨在提供数据值。它们不能在 SQL 语句中直接用作标识符或标识符的一部分,例如在需要 table 或数据库名称的上下文中,或用作保留字(例如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)
用户变量不能用于提供标识符的这一原则的一个 exception 是,当您构造字符串以用作准备语句以供以后执行时。在这种情况下,用户变量可用于提供语句的任何部分。下面的示例说明了如何完成此操作:
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)
有关更多信息,请参见第 13.5 节“准备好的语句”。
可以在应用程序中使用类似的技术来使用程序变量构造 SQL 语句,如下面使用 PHP 5 所示:
<?php
$mysqli = new mysqli("localhost", "user", "pass", "test");
if( mysqli_connect_errno() )
die("Connection failed: %s\n", mysqli_connect_error());
$col = "c1";
$query = "SELECT $col FROM t";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc())
{
echo "<p>" . $row["$col"] . "</p>\n";
}
$result->close();
$mysqli->close();
?>
以这种方式组装 SQL 语句有时被称为“动态 SQL”。