MySQL的语言结构 - 用户定义变量

林员外聊编程 2021-05-18

802

用户定义变量

可以将值存储在一条语句的用户定义变量中，然后在另一条语句中引用它。这样能够将值从一条语句传递到另一条语句。

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

用户定义变量是特定于会话的。一个客户端定义的用户变量不能被其他客户端看到或使用(例外：有权访问 Performance Schema 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，类型为字符串。

从 MySQL 8.0.22 开始，预编译语句中对用户变量的引用在第一次预编译语句时确定其类型，并在以后每次执行语句时保留此类型。类似地，存储过程中语句中使用的用户变量的类型在第一次调用存储过程时确定，并在每次后续调用时保留此类型。

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

MySQL 的早期版本允许 SET 以外的语句中为用户变量赋值。为了向后兼容，MySQL 8.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)

在应用程序中，可以使用类似的技术来使用程序变量构造 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”。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的语言结构 - 用户定义变量

评论