MySQL 變量使用規範與避坑指南：DECLARE 位置、作用域、大小寫與賦值最佳實踐

核心規範三大鐵律

鐵律一：DECLARE 必須置於塊首

CREATE PROCEDURE sp_correct()
BEGIN
    -- ✅ 聲明區：所有 DECLARE 集中在 BEGIN 之後
    DECLARE v_id INT;
    DECLARE v_name VARCHAR(50) DEFAULT 'Unknown';
    DECLARE v_total, v_count DECIMAL(10,2) DEFAULT 0;
    
    -- 業務區：SET/SELECT/流程控制等可執行語句
    SET v_id = 1;
    SELECT name INTO v_name FROM users WHERE id = v_id;
    ...
END;

CREATE PROCEDURE sp_wrong()
BEGIN
    SET v_id = 1;           -- ❌ 業務語句前置
    DECLARE v_id INT;       -- ❌ 聲明在業務之後 → ERROR 1327
    ...
END;

鐵律二：作用域嚴格限於聲明塊及巢狀子塊

CREATE PROCEDURE scope_demo()
BEGIN
    DECLARE v_outer INT DEFAULT 100;
    
    BEGIN  -- 子塊 1
        DECLARE v_inner INT DEFAULT 200;
        SELECT v_outer, v_inner;  -- 可見：100, 200
    END;
    
    BEGIN  -- 子塊 2（同級）
        -- SELECT v_inner;  -- ❌ 不可見：v_inner 已銷毀
        DECLARE v_inner INT DEFAULT 300;  -- ✅ 可重名，新變量
        SELECT v_outer, v_inner;  -- 可見：100, 300
    END;
    
    -- SELECT v_inner;  -- ❌ 外層不可見任何內層變量
END;

鐵律三：標識符不區分大小寫（變量名、關鍵字同理）

DECLARE v_UserName VARCHAR(50);
SET v_username = 'Test';      -- ✅ 等價
SELECT V_USERNAME;            -- ✅ 等價

BEGIN  -- 關鍵字同理
    declare v_id int;         -- ✅
    set v_id = 1;             -- ✅
    end;                      -- ✅
END;

SET 賦值語法規範

-- 標準語法
SET var_name = expr [, var_name = expr] ...;

-- 合法範例
SET v_a = 1;
SET v_a = 1, v_b = 'text', v_c = NOW();
SET v_x = v_y + 10;
SET v_flag = (SELECT COUNT(*) FROM t) > 0;

-- 表達式支援：函數、運算、子查詢（標量）、NULL

完整規範範本

CREATE PROCEDURE sp_template(IN p_param INT, OUT p_result VARCHAR(100))
BEGIN
    -- 1. 異常處理聲明（可選，但在 DECLARE 區首部）
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result = 'ERROR';
        ROLLBACK;
    END;
    
    -- 2. 局部變量聲明（按邏輯分組，加註釋）
    -- 業務主鍵
    DECLARE v_entity_id INT DEFAULT 0;
    -- 統計計數器
    DECLARE v_total_count INT DEFAULT 0;
    DECLARE v_success_count INT DEFAULT 0;
    -- 臨時存儲
    DECLARE v_temp_json JSON;
    
    -- 3. 參數驗證與初始化
    IF p_param <= 0 THEN
        SET p_result = 'INVALID_PARAM';
        LEAVE;  -- 需在標籤塊中使用
    END IF;
    
    -- 4. 核心業務邏輯
    START TRANSACTION;
    
    SELECT id INTO v_entity_id FROM entities WHERE id = p_param FOR UPDATE;
    
    IF v_entity_id IS NULL THEN
        SET p_result = 'NOT_FOUND';
        ROLLBACK;
    ELSE
        -- 業務處理...
        SET v_total_count = (SELECT COUNT(*) FROM related WHERE entity_id = v_entity_id);
        SET v_success_count = (SELECT COUNT(*) FROM related WHERE entity_id = v_entity_id AND status = 'OK');
        
        COMMIT;
        SET p_result = CONCAT('TOTAL:', v_total_count, ',OK:', v_success_count);
    END IF;
END;

避坑檢查清單

相關延伸主題

• MySQL DECLARE 語句完整指南
• 存儲過程中變量定義與賦值實戰
• MySQL 用戶變量與系統變量區別
• 存儲過程異常處理最佳實踐