MySQL :: MySQL 8.4 參考手冊 :: 11.4 使用者定義變數

版本 8.4

8.0 日文

11.4 使用者定義變數

您可以在一個語句中將值儲存在使用者定義變數中，然後在另一個語句中引用它。這使您可以將值從一個語句傳遞到另一個語句。

使用者變數寫為 @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 值和字串類型。

在預備語句中對使用者變數的引用，其類型在第一次準備語句時確定，並且在之後每次執行語句時都保留此類型。同樣地，在預存程序中使用的使用者變數的類型，在第一次叫用預存程序時確定，並且在之後每次叫用時都保留此類型。

使用者變數可以用於大多數允許表達式的地方。目前不包含明確要求常值的內容，例如 SELECT 語句的 LIMIT 子句，或 LOAD DATA 語句的 IGNORE N LINES 子句。

先前版本的 MySQL 允許在 SET 以外的語句中將值賦值給使用者變數。此功能在 MySQL 8.4 中為了向後相容而受到支援，但在 MySQL 的未來版本中可能會被移除。

以這種方式進行賦值時，您必須使用 := 作為賦值運算子；在 SET 以外的語句中，= 被視為比較運算子。

涉及使用者變數的表達式的求值順序是未定義的。例如，不保證 SELECT @a, @a:=@a+1 會先求值 @a，然後再執行賦值。

此外，變數的預設結果類型基於其在語句開頭的類型。如果變數在語句開頭時具有一種類型的值，但在同一語句中也賦予了不同類型的新值，則可能會產生意想不到的影響。

若要避免此行為的問題，請不要在單一語句中同時賦值和讀取相同變數的值，或者在使用變數之前將變數設定為 0、0.0 或 '' 來定義其類型。

當 HAVING、GROUP BY 和 ORDER BY 引用在 select 表達式清單中賦值的變數時，它們不會如預期般工作，因為表達式是在客戶端上求值的，因此可能會使用來自先前列的過時欄位值。

使用者變數旨在提供資料值。它們不能直接在 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」。