如何在 MySQL 中宣告和使用變數

MySQL 有三種變數機制。當同一個連線中的多個 SQL 陳述式需要共用值時，使用 @limit 這類使用者定義的工作階段變數。在預存程序、函式、觸發程序或事件中，使用不含 @ 的區域變數。需要檢查或設定伺服器或單一連線時，使用 @@SESSION.sql_mode 這類系統變數。

最重要的區別是，DECLARE 不是可以在 SQL 視窗中任意執行的一般陳述式。它只能出現在 BEGIN ... END 複合陳述式內，而且必須放在該區塊的可執行陳述式之前。一般指令碼請從 SET @name = value; 開始。SQL 範例中的關鍵字 DECLARE、SET、SELECT ... INTO、BEGIN ... END、SESSION 和 GLOBAL 保持原樣。

選擇正確的 MySQL 變數

這些形式不能互換。DECLARE @name INT 不是區域變數語法，而在預存程式外單獨執行 DECLARE name INT 會發生語法錯誤。

在 SQL 工作階段中使用使用者定義變數

使用者定義變數以 @ 開頭，不需要在使用前宣告。使用 SET 指派值或運算式，再在後續陳述式中參照它：

SET @low = 2, @high = 4;
SELECT n FROM demo_numbers WHERE n BETWEEN @low AND @high ORDER BY n;

變數只屬於目前連線。其他連線無法使用它，連線結束後變數也會消失。未初始化的使用者變數會得到 NULL，因此可重複使用的指令碼應在開始處初始化。

SET 是最清楚的指派形式。MySQL 也接受在 SET 陳述式中使用 :=：

SET @total = 10;
SET @total := @total + 5;
SELECT @total;

變數型別由指派的值決定，而不是由型別子句宣告。它可以保存數字、十進位值、字串、二進位字串或 NULL；跨越型別邊界時請使用明確轉型。

使用 SELECT ... INTO 載入查詢結果

當值來自查詢時，使用 SELECT ... INTO。目標變數數量必須與選取的運算式數量相同：

SELECT COUNT(*) INTO @number_of_rows
FROM demo_numbers;

SELECT @number_of_rows;

查詢應設計為只傳回一列。COUNT(*) 自然會傳回一列，使用唯一鍵查詢也很適合。如果可能傳回多列，請使用具有確定性的 ORDER BY 與 LIMIT 1 選出想要的列。

使用者變數不能取代資料表或欄位識別碼。SET @table_name = 'demo_numbers'; SELECT * FROM @table_name; 不會替換資料表名稱。若確實需要動態 SQL，請建立供 PREPARE 使用的陳述式，並驗證應用程式提供的識別碼。

MySQL 9.7 為了相容性仍接受 SET 以外的使用者變數指派，但該行為預計在未來移除。請使用 SET 和 SELECT ... INTO，不要使用 SELECT @n := n 這類運算式。

在預存程式中宣告區域變數

區域變數使用不含 @ 的 DECLARE，而且需要 MySQL 資料型別。可以指定 DEFAULT；否則初始值是 NULL。請在 BEGIN ... END 區塊開頭、所有可執行陳述式之前宣告。它們只在該區塊和巢狀區塊中可見。

以下程序使用輸入參數、區域變數和輸出參數。DELIMITER 指令屬於 mysql 用戶端，用來讓含有分號的程序本體以單一 CREATE PROCEDURE 陳述式送出。

DROP PROCEDURE IF EXISTS count_numbers;
DELIMITER //
CREATE PROCEDURE count_numbers(IN p_min INT, OUT p_count INT)
BEGIN
  DECLARE local_count INT DEFAULT 0;
  SELECT COUNT(*) INTO local_count
  FROM demo_numbers
  WHERE n >= p_min;
  SET p_count = local_count;
END//
DELIMITER ;
CALL count_numbers(3, @result);
SELECT @result;

local_count 只在程序執行期間存在。OUT 參數會透過傳給 CALL 的工作階段變數傳回。如果查詢選取多個欄位，請為每個運算式提供目標，並處理沒有相符資料列的情況。

可以在一個陳述式中宣告多個相同型別的變數，但分開宣告通常更容易維護：

DECLARE quantity, retries INT DEFAULT 0;
DECLARE label VARCHAR(100) DEFAULT 'pending';

DECLARE 也能定義條件、游標和處理常式。順序很重要：變數和條件在游標之前，游標在處理常式之前。因此在 SELECT 或 SET 之後宣告會造成語法錯誤。

了解 SELECT ... INTO 的資料列邊界

純量目標只能保存一個值，不能保存任意結果集。以下查詢刻意傳回三列，因此會產生錯誤1172：

SET @selected = 99;
SELECT n INTO @selected FROM demo_numbers ORDER BY n;

ERROR 1172 (42000) at line 6: Result consisted of more than one row

請將查詢縮小到已知的一列、彙總結果，或讓結果維持為資料列集合。只有在搭配有意義的排序時，LIMIT 1 才適用：

SELECT n INTO @selected
FROM demo_numbers
ORDER BY n DESC
LIMIT 1;

同一列規則也適用於區域變數。如果程序可能找不到資料列，請明確初始化變數，必要時使用 NOT FOUND 處理常式或存在性檢查。

檢查或設定 MySQL 系統變數

系統變數會設定伺服器或用戶端連線。使用 SHOW VARIABLES 或 @@ 限定詞檢查：

SHOW SESSION VARIABLES LIKE 'sql_mode';
SELECT @@SESSION.sql_mode, @@GLOBAL.sql_mode;

未指定範圍時，SHOW VARIABLES 使用目前工作階段。SESSION 值只影響該連線；GLOBAL 值通常會初始化新的連線。變更 GLOBAL 變數需要適當權限，而且該變數必須可變更。

SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
-- A privileged server administrator may use an appropriate GLOBAL setting.
-- SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES';

註解中的 GLOBAL 陳述式不包含在可執行 fixture 中，因為結果取決於帳戶權限以及變數的範圍和可變更性。

疑難排解無法運作的變數

• 如果 DECLARE 被拒絕，請確認它位於 BEGIN ... END 內且在所有可執行陳述式之前。一般 SQL 指令碼請使用 SET @name = value。
• 如果變數意外變成 NULL，請初始化它並檢查來源運算式或查詢。
• 如果 SELECT ... INTO 回報錯誤1172，請讓查詢只傳回一列，或保留結果為資料列集合。
• 其他連線看不到 @name 是正常的，因為使用者變數只屬於工作階段。
• 如果 @@GLOBAL.name 變更失敗，請檢查變數是否可變更以及管理權限。

成功 fixture 會指派工作階段變數、在篩選中重複使用它們、在程序中宣告區域變數並傳回輸出參數。邊界 fixture 會驗證多列 SELECT ... INTO 的診斷。兩者都使用暫時 MySQL 伺服器。

完整語法請參閱 MySQL 使用者定義變數參考、區域 DECLARE 參考 與 SET 變數指派參考。