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

版本 9.0

8.4 目前
8.0
5.7

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 9.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)

如需詳細資訊，請參閱第 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」。