MySQL :: MySQL 8.4 参考手册 :: 11.4 用户定义变量

版本 8.4

8.0 日语

11.4 用户定义变量

您可以在一个语句中将值存储在用户定义的变量中，并在另一个语句中稍后引用它。这使您能够将值从一个语句传递到另一个语句。

用户变量写为 @var_name，其中变量名 var_name 由字母数字字符、.、_ 和 $ 组成。如果将用户变量名作为字符串或标识符引用，则它可以包含其他字符（例如，@'my-var'、@"my-var" 或 @`my-var`）。

用户定义的变量是特定于会话的。一个客户端定义的用户变量不能被其他客户端看到或使用。（例外：有权访问性能模式 user_variables_by_thread 表的用户可以看到所有会话的所有用户变量。）当给定客户端会话的所有变量退出时，它们将自动释放。

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

设置用户定义变量的一种方法是发出 SET 语句

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

对于 SET，可以使用 = 或 := 作为赋值运算符。

可以为用户变量分配来自一组有限数据类型的值：整数、十进制、浮点数、二进制或非二进制字符串或 NULL 值。十进制和实数值的赋值不会保留值的精度或小数位数。与允许类型之一不同的类型的值将转换为允许类型。例如，具有时间或空间数据类型的值将转换为二进制字符串。具有 JSON 数据类型的值将转换为字符集为 utf8mb4 且排序规则为 utf8mb4_bin 的字符串。

如果为用户变量分配了非二进制（字符）字符串值，则它具有与字符串相同的字符集和排序规则。用户变量的强制性是隐式的。（这与表列值的强制性相同。）

分配给用户变量的十六进制或位值将被视为二进制字符串。要将十六进制或位值作为数字分配给用户变量，请在数字上下文中使用它。例如，添加 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 |
+------+------+------+

如果在结果集中选择了用户变量的值，则它将作为字符串返回给客户端。

如果引用尚未初始化的变量，则其值为 NULL，类型为字符串。

对准备好的语句中的用户变量的引用在其首次准备语句时确定其类型，并在每次执行该语句时保留此类型。类似地，在存储过程中语句中使用的用户变量的类型在首次调用存储过程时确定，并在每次后续调用中保留此类型。

用户变量可以在允许使用表达式的多数上下文中使用。这当前不包括明确需要字面值的上下文，例如在 SELECT 语句的 LIMIT 子句中，或 LOAD DATA 语句的 IGNORE N LINES 子句中。

以前版本的 MySQL 可以在 SET 以外的语句中为用户变量赋值。MySQL 8.4 中支持此功能以实现向后兼容性，但在未来版本的 MySQL 中可能会删除此功能。

以这种方式进行赋值时，必须使用 := 作为赋值运算符；在 SET 以外的语句中，= 被视为比较运算符。

涉及用户变量的表达式的求值顺序未定义。例如，不能保证 SELECT @a, @a:=@a+1 首先对 @a 求值，然后再执行赋值。

此外，变量的默认结果类型基于其在语句开头的类型。如果变量在语句开头保存一种类型的值，而在该语句中也为其分配了不同类型的新值，则这可能会产生意外的影响。

为避免此行为出现问题，请不要在单个语句中为同一变量赋值和读取其值，或者在使用变量之前将其设置为 0、0.0 或 '' 以定义其类型。

当引用在选择表达式列表中赋值的变量时，HAVING、GROUP BY 和 ORDER BY 无法按预期工作，因为表达式是在客户端上求值的，因此可以使用先前行中的过时列值。

用户变量旨在提供数据值。它们不能在 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)

有关更多信息，请参阅第 15.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”。