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

版本 9.0

8.4 当前
8.0
5.7

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 9.0 中支持此功能是为了向后兼容，但在未来版本的 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”。