MySQL5.7中英对照文档_用户变量 User-Defined Variables

163 阅读 0 评论 108 点赞

我是靠谱客的博主欣喜冥王星，这篇文章主要介绍MySQL5.7中英对照文档_用户变量 User-Defined Variables，现在分享给大家，希望可以做个参考。

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 @var_name, where the variable name var_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`).
用户变量的形式是 @var_name，其中变量名var_name可以由当前字符集的文字数字（alphanumeric）字符、‘.’、‘_’和‘$’组成。把当变量名当作字符串或标识符引用的时候，变量名也可以包含其它的字符（比如@'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.) All variables for a given client session are automatically freed when that client exits.
用户变量仅在当前会话中生效。当前会话中的用户变量不能被其它会话访问（例外情况：访问Performance Schema库中user_variables_by_thread 表是）。当会话关闭后，用户变量就会失效。

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语句设置用户变量的时候，可以使用“=”或者“:=”操作符
You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:
也可以不用SET语句来设置用户变量，在这张情况下，必须使用“=”操作符

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 |
+------+------+------+--------------------+

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.
用户变量可以接收整数、小数、浮点数、字符串（二进制或非二进制均可）、NULL类型的值。但是不能接收特别精确的数值。当其它类型的值被赋给用户变量时，MySQL会自动把它转换成用户变量可以接收的类型。

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.)
不太理解可压缩性（coercibility）什么意思。这句话的大意就是当用户变量被赋值字符串后，它就会被当做字符串看待。

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):
默认情况下，十六进制或二进制的值后被赋给用户变量后，就会被当做二进制字符串看待。当它在数值环境下使用时，会被当作数值。所谓数值环境就是+0，或者使用CAST函数（转换字段类型为数值）

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 |
+------+------+------+

-- 译者注：
-- 十六进值41转换成十进制是65
-- 字母A的十六进制ASCII编码是41
-- 二进值1000001转换成十进制是65
-- 字母A的二进制ASCII编码是1000001

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。

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 N LINES clause of a LOAD DATA statement.
用户变量可以被用在大多表达式中。但不包括明显需要文字值的表达式，例如SELECT语句的LIMIT子句，或者LOAD DATA语句的IGNORE number LINES子句。

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value with in the same statement. For example, to increment a variable, this is okay:
除了SET语句外，通常情况下，不应该在在一个语句，对用户变量同时采取赋值和读取的操作。如下这个例子可以实现变量的增长

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second：
在其他语句中，比如SELCT语句中，你可能得到预期的结果，也可能得不到。以如下语句举例，你期望处理第一个@a，然后再处理第二个@a

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

但，表达式中并没有告诉如何处理这些@a的次序。（译者注：我猜意思是，如果在表达式中确定处理变量的次序（比如利用 Case…When语句）那么就会得到预期的结果。

Another issue with assigning a value to a variable and reading the value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement. The following example illustrates this:

在非SET的一个语句中，赋值并读取同一个用户变量的默认类型取决于语句前面的变量类型。下面的例子说明了该点：

mysql> SET @a='test';
mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is a string and converts all accesses of @a to strings, even though @a is set to a number for the second row. After the SELECT statement executes, @a is regarded as a number for the next statement.
在第一行SELECT语句中，MySQL告诉客户端将@a是字符串类型的。在第二行SELECT语句中，@a被设置成一个数字。执行完这行SELECT语句后，@a就会被下一个语句认为是一个数字。

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或者''以定义其类型。

In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:
在SELECT语句中，所有的表达式只有发送到客户端后才会被执行。这意味着在HAVING、GROUP BY或者ORDER BY子句中，不能使用包含SELECT语句中设置的表达式。（译者注：在SQL语句的执行顺序中，先执行HAVING、GROUP BY或者ORDER BY，再执行SELECT）如下语句是不能被执行的

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.
HAVING子句中的b中引用了SELECT表达式中的@aa。因为@aa包含了之前行id的值，而不是当前行的，所以不能按期望执行。

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等。即使是用户变量被引用也不可以。

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;
+----+
Expression Syntax
1503
| c1 |
+----+
| 0 |
+----+
| 1 |
+----+
2 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

写在最后：
翻译这些本来是期望可以理解，使用用户变量来完成排名名次的原理，然帮助有限