您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程調試實戰：基於內存臨時表的 DEBUG 日誌框架

編輯：MySQL 綜合教學

MySQL 存儲過程調試實戰：基於內存臨時表的 DEBUG 日誌框架

存儲過程在資料庫內部執行，無法像應用代碼那樣使用斷點、變量監視。本文介紹一套輕量級、可復用的調試框架，利用 MEMORY 引擎臨時表記錄執行軌跡、變量值，最終轉存持久表供事後分析，解決「盒子裡看不見」的痛點。

一、核心設計思路

零磁盤 IO：臨時表用 ENGINE=MEMORY，極速寫入，不影響主業務效能。
線程隔離：記錄 CONNECTION_ID()，多併發調試互不干擾。
自動清理：會話結束自動銷毀臨時表，或手動 cleanup() 轉存持久表。
異常安全：DECLARE CONTINUE HANDLER 捕獲表不存在自動重建，避免調試代碼本身拋錯。

二、框架三大核心過程

1. setupProcLog()：初始化

CREATE PROCEDURE setupProcLog()
BEGIN
  DECLARE v_exists INT DEFAULT 0;
  -- 檢查持久日誌表
  SELECT COUNT(*) INTO v_exists
  FROM information_schema.tables
  WHERE table_schema = DATABASE() AND table_name = 'proclog';
  IF v_exists = 0 THEN
    CREATE TABLE proclog (
      entrytime DATETIME,
      connection_id INT NOT NULL DEFAULT 0,
      msg VARCHAR(512)
    ) ENGINE=InnoDB;  -- 持久表用 InnoDB，避免事務回滾丟失日誌
  END IF;
  -- 建立臨時記憶體表
  CREATE TEMPORARY TABLE IF NOT EXISTS tmp_proclog (
    entrytime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    connection_id INT NOT NULL DEFAULT 0,
    msg VARCHAR(512)
  ) ENGINE=MEMORY;
END;

2. procLog(msg)：記錄日誌

CREATE PROCEDURE procLog(IN p_msg VARCHAR(512))
BEGIN
  DECLARE CONTINUE HANDLER FOR 1146  -- 表不存在
  BEGIN
    CALL setupProcLog();
    INSERT INTO tmp_proclog (connection_id, msg) VALUES (CONNECTION_ID(), 'tmp_proclog rebuilt');
  END;

  INSERT INTO tmp_proclog (connection_id, msg)
  VALUES (CONNECTION_ID(), p_msg);
END;

3. cleanup(msg)：持久化並清理

CREATE PROCEDURE cleanup(IN p_msg VARCHAR(512))
BEGIN
  CALL procLog(CONCAT('cleanup: ', IFNULL(p_msg, '')));
  INSERT INTO proclog SELECT * FROM tmp_proclog;
  DROP TEMPORARY TABLE IF EXISTS tmp_proclog;
END;

三、在業務過程中使用

CREATE PROCEDURE sp_business_demo(IN p_user VARCHAR(32))
BEGIN
  DECLARE v_status INT DEFAULT 0;
  DECLARE v_counter INT DEFAULT 0;

  -- 異常處理：確保日誌持久化
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    SET v_status = -1;
    ROLLBACK;
    CALL cleanup('exception handler');
    SELECT v_status AS status_code;
  END;

  CALL setupProcLog();
  CALL procLog('>>> ENTER sp_business_demo');

  START TRANSACTION;
  CALL procLog(CONCAT('START TRANSACTION, status=', v_status));

  -- 模擬迴圈業務
  loop_label: LOOP
    CALL procLog(CONCAT('Loop iter: ', v_counter));
    IF v_counter > 5 THEN LEAVE loop_label; END IF;

    INSERT INTO userloop_count (username, cnt) VALUES (p_user, v_counter);
    CALL procLog('INSERT userloop_count done');

    -- 呼叫子過程 (可能隱式提交！)
    CALL sp_child_proc(RAND(), p_user, @bail);
    CALL procLog(CONCAT('Child returned bail=', @bail));

    IF @bail = 1 THEN
      SET v_status = 1;
      LEAVE loop_label;
    END IF;
    SET v_counter = v_counter + 1;
  END LOOP;

  -- 最終提交/回滾決策
  IF v_status = 0 THEN COMMIT; ELSE ROLLBACK; END IF;
  CALL procLog(CONCAT('Final status=', v_status));

  CALL cleanup('normal end');
  SELECT v_status AS status_code;
END;

四、實戰排查：隱式提交陷阱

通過 procLog 記錄的行號與 proclog 表輸出，結合 SHOW BINLOG EVENTS 或 mysqlbinlog，可精準定位「子過程中的 START TRANSACTION 導致外層事務隱式提交」等經典問題。二進制日誌會記錄每條 DML 的線程 ID、時間戳，是終極證據。