MySQL 8.4 Release Notes
你可以在一个语句中将值存储在用户定义的变量中，并在另一个语句中引用它。这使你能够将值从一个语句传递到另一个语句。You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.
用户变量的写法是 @，其中变量名 var_namevar_name 由字母数字字符、.、_ 和 $ 组成。如果将用户变量名作为字符串或标识符引用，它还可以包含其他字符（例如 @'my-var'、@"my-var" 或 @`my-var`）。User variables are written as
@, where the
variable name var_namevar_name consists of
alphanumeric characters, .,
_, and $. A user variable
name can contain other characters if you quote it as a string or
identifier (for example, @'my-var',
@"my-var", or @`my-var`).
用户定义的变量是会话特定的。一个客户端定义的用户变量无法被其他客户端看到或使用。（例外：具有访问性能模式 user_variables_by_thread 表的用户可以看到所有会话的所有用户变量。）当客户端退出时，该客户端会话的所有变量会自动释放。User-defined variables are session specific. A user variable
defined by one client cannot be seen or used by other clients.
(Exception: A user with access to the Performance Schema
user_variables_by_thread table can
see all user variables for all sessions.) All variables for a
given client session are automatically freed when that client
exits.
用户变量名不区分大小写。名称的最大长度为 64 个字符。User variable names are not case-sensitive. Names have a maximum length of 64 characters.
设置用户定义变量的一种方法是发出SET语句：One way to set a user-defined variable is by issuing a
SET
statement:
SET @var_name=expr[, @var_name=expr] ...
对于SET，赋值运算符可以使用=或:=。For SET,
either = or
:= can be
used as the assignment operator.
用户变量可以从有限的数据类型集合中分配一个值：整数、小数、浮点数、二进制或非二进制字符串，或NULL值。分配小数和实数值不会保留值的精度或规模。类型不是允许类型之一的值会被转换为允许的类型。例如，具有时间类型或空间数据类型的值会被转换为二进制字符串。具有JSON数据类型的值会被转换为字符集为utf8mb4且校对规则为utf8mb4_bin的字符串。User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary
string, or NULL value. Assignment of decimal
and real values does not preserve the precision or scale of the
value. A value of a type other than one of the permissible types
is converted to a permissible type. For example, a value having a
temporal or spatial data type is converted to a binary string. A
value having the JSON data type is
converted to a string with a character set of
utf8mb4 and a collation of
utf8mb4_bin.
如果给用户变量分配了非二进制（字符）字符串值，它具有与该字符串相同的字符集和排序规则。用户变量的强制性是隐式的。（这与表列值的强制性相同。）If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)
分配给用户变量的十六进制或位值被视为二进制字符串。要将十六进制或位值作为数字分配给用户变量，请在数字上下文中使用它。例如，加 0 或使用 CAST(... AS UNSIGNED)：Hexadecimal or bit values assigned to user variables are treated
as binary strings. To assign a hexadecimal or bit value as a
number to a user variable, use it in numeric context. For example,
add 0 or use 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 | +------+------+------+
如果在结果集中选择了用户变量的值，它将作为字符串返回给客户端。If the value of a user variable is selected in a result set, it is returned to the client as a string.
如果你引用一个尚未初始化的变量，它的值为NULL，类型为字符串。If you refer to a variable that has not been initialized, it has a
value of NULL and a type of string.
在预处理语句中引用用户变量时，其类型在语句首次准备时确定，并在此后的每次执行中保持该类型。同样，在存储过程中使用的语句中的用户变量的类型在存储过程首次调用时确定，并在随后的每次调用中保持该类型。A reference to a user variable in a prepared statement has its type determined when the statement is first prepared, and retains this type each time the statement is executed thereafter. Similarly, the type of a user variable employed in a statement within a stored procedure is determined the first time the stored procedure is invoked, and retains this type with each subsequent invocation.
在大多数允许使用表达式的上下文中，可以使用用户变量。当前，这并不包括明确要求字面值的上下文，例如在 SELECT 语句的 LIMIT 子句中，或者在 LOAD DATA 语句的 IGNORE 子句中。User variables may be used in most contexts where expressions are
permitted. This does not currently include contexts that
explicitly require a literal value, such as in the
N LINESLIMIT clause of a
SELECT statement, or the
IGNORE
clause of a N LINESLOAD DATA statement.
早期版本的 MySQL 允许在除了 SET 之外的语句中为用户变量赋值。MySQL 8.4 为了向后兼容仍然支持此功能，但未来的 MySQL 版本可能会移除此功能。Previous releases of MySQL made it possible to assign a value to a
user variable in statements other than
SET. This
functionality is supported in MySQL 8.4 for backward
compatibility but is subject to removal in a future release of
MySQL.
以这种方式进行赋值时，必须使用 := 作为赋值运算符；= 在 SET 以外的语句中被视为比较运算符。When making an assignment in this way, you must use
:= as the
assignment operator; = is treated as the
comparison operator in statements other than
SET.
涉及用户变量的表达式的求值顺序是不确定的。例如，不能保证 SELECT @a, @a:=@a+1 先求值 @a，然后再执行赋值。The order of evaluation for expressions involving user variables
is undefined. For example, there is no guarantee that
SELECT @a, @a:=@a+1 evaluates
@a first and then performs the assignment.
此外，变量的默认结果类型是基于语句开始时的类型。如果变量在语句开始时持有某种类型的值，并且在该语句中被赋予不同类型的新值，这可能会产生意想不到的效果。In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.
为了避免这种行为带来的问题，要么不要在同一条语句中对同一个变量赋值并读取其值，要么在使用之前将变量设置为0、0.0或''以定义其类型。To avoid problems with this behavior, either do not assign a value
to and read the value of the same variable within a single
statement, or else set the variable to 0,
0.0, or '' to define its
type before you use it.
HAVING、GROUP BY和ORDER BY在引用在 SELECT 表达式列表中被赋值的变量时不会按预期工作，因为表达式是在客户端计算的，因此可能会使用来自前一行的陈旧列值。HAVING, GROUP BY, and
ORDER BY, when referring to a variable that is
assigned a value in the select expression list do not work as
expected because the expression is evaluated on the client and
thus can use stale column values from a previous row.
用户变量用于提供数据值。它们不能直接在 SQL 语句中用作标识符或作为标识符的一部分，例如在需要表或数据库名称的上下文中，或作为保留字，例如 SELECT。即使变量被引用，如下例所示，这条规则仍然适用：User variables are intended to provide data values. They cannot be
used directly in an SQL statement as an identifier or as part of
an identifier, such as in contexts where a table or database name
is expected, or as a reserved word such as
SELECT. This is true even if the
variable is quoted, as shown in the following example:
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)
关于用户变量不能用于提供标识符的这一原则的一个例外情况是，当您构建一个字符串以便稍后用作准备语句时。在这种情况下，用户变量可以用来提供语句的任何部分。以下示例说明了如何做到这一点：An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:
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 节，“预处理语句”，了解更多信息。See Section 15.5, “Prepared Statements”, for more information.
在应用程序中也可以使用类似的技术，通过程序变量构建 SQL 语句，如下面使用 PHP 5 所示：A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using 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”。Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.