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.您可以将值存储在一个语句中的用户定义变量中，然后在另一个语句中将其引用。这使您能够将值从一个语句传递到另一个语句。
User variables are written as 用户变量被写成@, where the variable name var_namevar_name consists of alphanumeric characters, ., _, and $. @var_name，其中变量名var_name由字母数字字符、.、_、,和$构成。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`).@'my-var'、@"my-var"或@`my-var`）。
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.) user_variables_by_thread表的用户可以查看所有会话的所有用户变量。）All variables for a given client session are automatically freed when that client exits.给定客户端会话的所有变量都会在该客户端退出时自动释放。
User variable names are not case-sensitive. Names have a maximum length of 64 characters.用户变量名不区分大小写。名称的最大长度为64个字符。
One way to set a user-defined variable is by issuing a 设置用户定义变量的一种方法是发出SET statement:SET语句：
SET @var_name=expr[, @var_name=expr] ...
For 对于SET, either = or := can be used as the assignment operator.SET，可以使用=或:=作为赋值运算符。
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. NULL值。小数和实数的赋值不会保留值的精度或小数位数。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.JSON数据类型的值被转换为字符集为utf8mb4、排序规则为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.)如果用户变量被分配了一个非二进制（字符）字符串值，那么它与字符串具有相同的字符集和排序规则。用户变量的强制性是隐含的。（这与表列值的矫顽力相同。）
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 分配给用户变量的十六进制或位值被视为二进制字符串。要将十六进制或位值作为数字分配给用户变量，请在数字上下文中使用它。例如，添加0或使用CAST(... AS UNSIGNED):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.如果在结果集中选择了用户变量的值，则会将其作为字符串返回给客户端。
If you refer to a variable that has not been initialized, it has a value of 如果引用的变量尚未初始化，则其值为NULL and a type of string.NULL，类型为字符串。
Beginning with MySQL 8.0.22, 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.从MySQL 8.0.22开始，对已准备语句中用户变量的引用在首次准备语句时确定其类型，并在此后每次执行语句时保留此类型。类似地，存储过程中语句中使用的用户变量的类型是在第一次调用存储过程时确定的，并在每次后续调用时保留此类型。
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 用户变量可以用于允许表达式的大多数上下文中。这目前不包括明确需要文字值的上下文，例如LIMIT clause of a SELECT statement, or the IGNORE clause of a N LINESLOAD DATA statement.SELECT语句的LIMIT子句或LOAD DATA语句的IGNORE N LINES子句。
Previous releases of MySQL made it possible to assign a value to a user variable in statements other than MySQL的早期版本允许在SET. SET以外的语句中为用户变量赋值。This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.MySQL 8.0支持此功能以实现向后兼容性，但可能会在将来的MySQL版本中删除。
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.:=作为赋值运算符；=在SET以外的语句中被视为比较运算符。
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.SELECT @a, @a:=@a+1首先计算@a，然后执行赋值。
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.此外，变量的默认结果类型基于语句开头的类型。如果变量在语句的开头保留一个类型的值，并且在语句中还为其分配了一个不同类型的新值，则这可能会产生意外的影响。
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.0、0.0或''以定义其类型，然后再使用它。
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.HAVING、GROUP BY和ORDER BY在引用在选择表达式列表中分配了值的变量时不能按预期工作，因为该表达式是在客户端上计算的，因此可以使用前一行中过时的列值。
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 用户变量旨在提供数据值。它们不能在SQL语句中直接用作标识符或标识符的一部分，例如在需要表或数据库名称的上下文中，或者用作保留字（如SELECT. This is true even if the variable is quoted, as shown in the following example: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)
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)
See Section 13.5, “Prepared Statements”, for more information.有关更多信息，请参阅第13.5节，“准备好的报表”。
A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 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();
?>
Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.以这种方式组装SQL语句有时被称为“动态SQL”。