MySQL 儲存過程變數聲明與使用詳解

前言

MySQL 儲存過程支援三種變數類型：區域變數、會話變數、全域變數。理解其作用域、生命週期與賦值方式，是編寫高效儲存過程的基礎。

一、區域變數

語法

DECLARE var_name data_type [DEFAULT default_value];
-- 多變數同類型可合併
DECLARE x, y INT DEFAULT 0;

特性

• 僅在 BEGIN ... END 區塊內有效。
• 必須在儲存過程開頭、任何其他語句之前聲明。
• 區分大小寫（依 lower_case_table_names 設定）。
• 預設值為 NULL（未指定 DEFAULT 時）。

範例

CREATE PROCEDURE sp_local_var_demo()
BEGIN
  DECLARE v_count INT DEFAULT 0;
  DECLARE v_name VARCHAR(50) DEFAULT 'test';
  
  SET v_count = v_count + 1;
  SELECT v_count, v_name;
END;

二、會話變數

語法

SET @var_name = value;
-- 或
SELECT @var_name := value;

特性

• 前綴 @，無需聲明，MySQL 自動推斷型別。
• 作用域：當前連線（Session）全域可見。
• 生命週期：連線斷開或執行 SET @var = NULL 時銷毀。
• 可在儲存過程、觸發器、函數、多條 SQL 語句間傳遞值。

範例

SET @total = 0;
SELECT @total := @total + 1 AS seq, id FROM users LIMIT 5;
-- 產生連續序號

三、全域變數

語法

SET GLOBAL var_name = value;
SET @@global.var_name = value;
-- 僅限系統變數，不可自訂

特性

• 影響所有新連線，不影響已建立連線。
• 需 SUPER 權限（MySQL 5.7+ 需 SYSTEM_VARIABLES_ADMIN）。
• 持久化需修改 my.cnf 或使用 SET PERSIST (8.0+)。

四、變數賦值方式對比

SELECT INTO 範例

CREATE PROCEDURE sp_get_max_id(OUT p_max_id INT)
BEGIN
  SELECT MAX(id) INTO p_max_id FROM orders;
END;

五、常見陷阱與最佳實踐

1. 區域變數 vs 會話變數命名衝突： 區域變數優先，建議區域變數用 v_ 前綴，會話變數用 @s_。
2. DECLARE 位置錯誤： 必須在 BEGIN 之後、第一條執行語句之前。
3. 未初始化直接使用： 區域變數預設 NULL，數學運算結果為 NULL，務必 DEFAULT 0。
4. 會話變數洩漏： 長連線池中會話變數可能殘留，關鍵邏輯前顯式重置。
5. 在 Prepared Statement 中使用： 僅支援會話變數 SET @a = 1; PREPARE stmt FROM 'SELECT @a';。

六、完整範例：帶參數與變數的儲存過程

DELIMITER //
CREATE PROCEDURE sp_order_stats(IN p_days INT, OUT p_total DECIMAL(10,2), OUT p_avg DECIMAL(10,2))
BEGIN
  DECLARE v_start_date DATE;
  SET v_start_date = DATE_SUB(CURDATE(), INTERVAL p_days DAY);
  
  SELECT 
    COALESCE(SUM(amount), 0),
    COALESCE(AVG(amount), 0)
  INTO p_total, p_avg
  FROM orders
  WHERE create_time >= v_start_date;
END //
DELIMITER ;

-- 呼叫
CALL sp_order_stats(30, @total, @avg);
SELECT @total, @avg;

善用三類變數，能讓儲存過程邏輯更清晰、效能更優、維護更容易。