MySQL 變量詳解：用戶變量(@var) vs 局部變量(var) 核心差異與陷阱

MySQL 變量詳解：用戶變量(@var) vs 局部變量(var) 核心差異與陷阱

MySQL 擁有兩種截然不同的變量體系：用戶定義變量 (User-Defined Variables，@var) 與 存儲程序局部變量 (Local Variables，var)。混淆兩者會導致難以排查的 Bug。本文從作用域、生命週期、初始化、類型系統等維度深度對比。

一、核心差異速查表

二、行為差異演示

-- 建立測試過程
DELIMITER $$
CREATE PROCEDURE sp_var_demo()
BEGIN
  DECLARE v_local INT DEFAULT 1;   -- 局部變量
  SET v_local = v_local + 1;
  SET @v_session = @v_session + 1; -- 用戶變量
  SELECT v_local AS local_var, @v_session AS session_var;
END$$
DELIMITER ;

-- 初始化用戶變量
SET @v_session = 1;

-- 首次調用
CALL sp_var_demo();
-- 結果: local_var=2, session_var=2

-- 二次調用 (局部變量重置，用戶變量保持)
CALL sp_var_demo();
-- 結果: local_var=2, session_var=3

-- 三次調用
CALL sp_var_demo();
-- 結果: local_var=2, session_var=4

三、常見陷阱與避坑指南

1. CONCAT 遇 NULL 失效

-- 錯誤：若 @v 為 NULL，結果為 NULL
SELECT CONCAT('value=', @v);

-- 正確：使用 IFNULL/COALESCE
SELECT CONCAT('value=', IFNULL(@v, 'NULL'));

2. 用戶變量類型不可預測

SET @x = '123';   -- 字符串
SET @x = @x + 1;  -- 自動轉為數字 124
SET @x = CONCAT(@x, 'abc'); -- 又變字符串 '124abc'

建議：關鍵邏輯顯式 CAST(@x AS UNSIGNED) 或改用局部變量。

3. 會話變量洩露導致併發干擾

連接池復用連線時，上一個請求殘留的 @var 會影響下一個請求。解決：存儲過程入口統一 SET @var = NULL 初始化，或改用局部變量。

4. SELECT ... INTO 變量名衝突

DECLARE v_id INT;
SELECT id INTO v_id FROM t WHERE ...;  -- 若表有 id 欄位，無歧義
-- 若表也有 v_id 欄位，需加限定符
SELECT t.id INTO v_id FROM t ...;

四、系統變量補充

除了上述兩類，MySQL 還有 系統變量：

• @@global.sort_buffer_size：全局配置，需 SUPER 權限修改，新連線生效。
• @@session.sql_mode：會話級，當前連線立即生效，SET SESSION sql_mode = 'STRICT_TRANS_TABLES'。
• 系統變量與用戶變量無關，勿混淆。

五、選型決策樹

1. 需在多個存儲過程/觸發器間共享狀態？→ 用戶變量 @var (謹慎使用，文檔化)。
2. 僅在單個過程/函數/觸發器內部使用？→ 局部變量 DECLARE var (首選)。
3. 需修改全局配置影響所有連線？→ 系統變量 @@global.xxx (需權限)。
4. 需修改當前會話行為？→ 系統變量 @@session.xxx。

六、最佳實踐

• 默認用局部變量：作用域清晰、類型安全、無併發干擾。
• 用戶變量僅用於：跨過程傳遞簡單狀態、調試輸出、動態 SQL 構建中佔位。
• 命名規範：局部變量 v_ 開頭，用戶變量 @v_ 開頭，系統變量直接 @@。
• 文檔化共享變量：若業務必須用 @var 跨過程傳值，在過程頭部註釋「輸入 @v_xxx、輸出 @v_yyy」。

搞清三類變量的邊界，是編寫穩健、可維護 MySQL 存儲代碼的基礎功力。