MySQL 使用者變數賦值完整語法與陷阱避坑指南

MySQL 使用者變數賦值完整語法與陷阱避坑指南

MySQL 使用者變數（User-Defined Variables）以 @var_name 形式表示，屬於 Session 級別，無需預先宣告即可使用。本文整理官方文件規範，詳細說明 SET/SELECT 賦值語法差異、型別轉換、同語句賦值與讀取的未定義行為、以及預處理語句中的動態用法。

一、基本賦值語法

1. SET 陳述式（支援 = 或 :=）

SET @a = 1, @b = 2, @c := 3;
SET @x = 'hello', @y = @x;

2. SELECT 等非 SET 陳述式（必須用 :=）

SELECT @a := 1, @b := 2, @c := @a + @b;
SELECT @id := id FROM users WHERE email = 'test@example.com';

關鍵差異： 在非 SET 陳述式中，= 被視為比較運算子，賦值必須用 :=。

二、支援的資料型別與轉換

• 整數、定點數、浮點數
• 二進位/非二進位字串
• NULL
• 不支援： 時態型別（DATE/TIME/DATETIME）→ 轉為二進位字串；JSON → 轉為 utf8mb4 字串

SET @v1 = b'1000001';        -- 二進位字串 'A'
SET @v2 = CAST(b'1000001' AS UNSIGNED); -- 數值 65
SET @v3 = b'1000001' + 0;    -- 數值 65

三、Session 隔離與生命週期

• 變數僅在目前連線可見，其他 Client 不可存取
• 連線結束自動釋放
• Performance Schema user_variables_by_thread 表可跨 Session 查看（需權限）

四、重大陷阱：同一陳述式中賦值與讀取順序未定義

-- 錯誤示範：順序未保證
SELECT @a, @a := @a + 1, ...;

-- 正確做法：分開陳述式
SET @a = @a + 1;
SELECT @a;

MySQL 不保證運算式評估順序，同語句中賦值後立即讀取可能得到舊值或新值，視優化器決定而定。

五、型別由首次賦值決定，後續隱含轉換

SET @a = 'test';           -- 型別為字串
SELECT @a, (@a := 20) FROM t;
-- 結果欄位型別仍為字串，20 會轉為 '20'

解決： 先以 0、0.0、'' 初始化確立型別，再進行運算。

六、無法用於識別符（資料表名、欄位名等）

SET @col = 'c1';
SELECT @col FROM t;        -- 回傳字串 'c1'
SELECT `@col` FROM t;      -- 錯誤：Unknown column '@col'

例外：動態 SQL 預處理語句中可拼接變數構建識別符。

七、動態 SQL 預處理應用

SET @c = 'c1';
SET @s = CONCAT('SELECT ', @c, ' FROM t');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

八、官方文件參考

MySQL 5.7 Reference Manual: User-Defined Variables

掌握上述規則，可避開 MySQL 使用者變數的常見陷阱，撰寫穩健的動態 SQL 與程序邏輯。