您可以将值存储在一个语句中的用户定义变量中,然后在另一个语句中引用它。这使您能够将值从一个语句传递到另一个语句。
用户变量被写为@var_name
,其中变量名由字母数字字符、.(点)、_和$组成。如果将用户变量名称作为字符串或标识符(例如, @'my-var'
, @"my-var"
, or @`my-var`
)引用,则它可以包含其他字符。
用户定义的变量是特定于会话的。一个客户端定义的用户变量不能被其他客户端看到或使用。(例外:用户访问性能架构( Performance Schema)的 user_variables_by_thread
表可以查看所有会话的所有用户变量。)当客户端退出时,给定客户端会话的所有变量都自动释放。
用户变量名不区分大小写。名称的最大长度为64个字符。
设置用户定义变量的一种方法是发出set语句:
SET @var_name = expr [, @var_name = expr] ...
对于SET,可以使用=或:=作为赋值运算符。
用户变量可以从一组有限的数据类型中分配一个值:整数、十进制、浮点、二进制或非二进制字符串或空值。小数和实数的赋值不保留值的精度或小数位数。非允许类型的值转换为允许类型。例如,具有时间或空间数据类型的值被转换为二进制字符串。具有JSON数据类型的值转换为字符集为utf8mb4、排序规则为utf8mb4的字符串。
如果给用户变量分配了一个非二进制(字符)字符串值,则该变量具有与字符串相同的字符集和排序规则。用户变量的强制性是隐式的。(这与表列值的强制性相同。)
分配给用户变量的十六进制或位值被视为二进制字符串。要将十六进制或位值作为数字分配给用户变量,请在数字上下文中使用它。例如,添加0或使用 CAST(... AS UNSIGNED)
:
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 |
+------+------+------+
如果在结果集中选择了用户变量的值,则将其作为字符串返回给客户端。
如果引用尚未初始化的变量,则该变量的值为空,类型为字符串。
用户变量可以在大多数允许表达式的上下文中使用。这当前不包括显式要求文本值的上下文,例如在SELECT语句的LIMIT子句中,或在LOAD DATA语句的IGNORE N LINES子句中。
也可以在SET以外的语句中为用户变量赋值。(此功能在MySQL8.0中不推荐使用,以后的版本中可能会删除此功能。)以这种方式进行赋值时,赋值运算符必须是(:=)和(not=)因为后者被视为集合以外的语句中的比较运算符=
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, ...;
但是,涉及用户变量的表达式的求值顺序是未定义的。
给变量赋值并在同一个非SET语句中读取值的另一个问题是,变量的默认结果类型基于语句开头的类型。以下示例说明了这一点:
mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;
对于这个SELECT语句,MySQL向客户机报告第一列是一个字符串,并将对@a的所有访问转换为字符串,即使第二行的@a被设置为一个数字。执行SELECT语句后,@a在下一条语句中被视为数字。
若要避免此行为出现问题,请不要在单个语句中为同一变量赋值并读取该变量的值,或者在使用该变量之前将其设置为0、0.0或“”以定义其类型。
在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子句中对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)
应用程序中也可以使用类似的技术,使用程序变量构造SQL语句,如使用PHP5所示:
<?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();
?>v
以这种方式组装SQL语句有时称为“动态SQL”。