MySQL 5.7 錯誤診斷區 (Diagnostics Area) 與 GET STACKED DIAGNOSTICS 實戰

MySQL 5.7 錯誤診斷區 (Diagnostics Area) 與 GET STACKED DIAGNOSTICS 實戰

MySQL 5.7 引入 Diagnostics Area Stack，將錯誤信息以堆疊方式保存，重置條件更寬鬆，配合 GET STACKED DIAGNOSTICS 可在存儲過程中優雅捕獲多條錯誤，避免 5.6 需動態 SQL 拼接的繁瑣。本文對比 5.6/5.7 寫法並給出生產可用範例。

表結構準備

CREATE TABLE `t_datetime` (
  `id` INT(11) NOT NULL,
  `log_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `end_time` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE tb_log (
  errorno INT,
  errortext TEXT,
  error_timestamp DATETIME
);

MySQL 5.6 寫法：繁瑣的動態 SQL

DELIMITER $$
CREATE PROCEDURE `sp_do_insert`(
  IN f_id INT, IN f_log_time VARCHAR(255), IN f_end_time VARCHAR(255)
)
BEGIN
  DECLARE done1 TINYINT DEFAULT 0;
  DECLARE i TINYINT DEFAULT 1;
  DECLARE v_errcount INT DEFAULT 0;
  DECLARE v_errno INT DEFAULT 0;
  DECLARE v_msg TEXT;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    SET done1 = 1;
    GET DIAGNOSTICS v_errcount = NUMBER;
    SET v_msg = '';
    WHILE i <= v_errcount DO
      GET DIAGNOSTICS CONDITION i v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
      SET @stmt = CONCAT('SELECT ', v_errno, ',"', v_msg, '","', NOW(), '" INTO @errno', i, ',@msg', i, ',@log_timestamp', i, ';');
      PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;
      SET i = i + 1;
    END WHILE;
  END;

  INSERT INTO t_datetime (id, log_time, end_time) VALUES (f_id, f_log_time, f_end_time);

  IF done1 = 1 THEN
    SET i = 1;
    WHILE i <= v_errcount DO
      SET @stmt = CONCAT('INSERT INTO tb_log SELECT @errno', i, ',@msg', i, ',@log_timestamp', i, ';');
      PREPARE s1 FROM @stmt; EXECUTE s1; DEALLOCATE PREPARE s1;
      SET i = i + 1;
    END WHILE;
  END IF;
END$$
DELIMITER ;

痛點：必須用用戶變數 @errno1 等中轉，動態 PREPARE 易錯、難維護。

MySQL 5.7 寫法：簡潔的 STACKED DIAGNOSTICS

DELIMITER $$
CREATE PROCEDURE `sp_do_insert`(
  IN f_id INT, IN f_log_time VARCHAR(255), IN f_end_time VARCHAR(255)
)
BEGIN
  DECLARE i TINYINT DEFAULT 1;
  DECLARE v_errcount INT DEFAULT 0;
  DECLARE v_errno INT DEFAULT 0;
  DECLARE v_msg TEXT;

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  BEGIN
    GET STACKED DIAGNOSTICS v_errcount = NUMBER;
    WHILE i <= v_errcount DO
      GET STACKED DIAGNOSTICS CONDITION i
        v_errno = MYSQL_ERRNO,
        v_msg = MESSAGE_TEXT;
      INSERT INTO tb_log VALUES (v_errno, v_msg, NOW());
      SET i = i + 1;
    END WHILE;
  END;

  INSERT INTO t_datetime (id, log_time, end_time) VALUES (f_id, f_log_time, f_end_time);
END$$
DELIMITER ;

關鍵差異：

• GET STACKED DIAGNOSTICS 直接讀取堆疊區，無需用戶變數中轉。
• 可直接 INSERT INTO tb_log，邏輯線性、可讀性強。
• 堆疊區在 Handler 結束前不會被重置，支援多條錯誤捕獲（如範例同時觸發 Data truncated ×2 + Duplicate entry）。

執行驗證

CALL sp_do_insert(2, '4', '5');
-- Query OK, 1 row affected (0.01 sec)

SELECT * FROM tb_log\G
*************************** 1. row ***************************
errorno: 1265
errortext: Data truncated for column 'log_time' at row 1
error_timestamp: 2015-11-17 11:53:10
*************************** 2. row ***************************
errorno: 1265
errortext: Data truncated for column 'end_time' at row 1
error_timestamp: 2015-11-17 11:53:10
*************************** 3. row ***************************
errorno: 1062
errortext: Duplicate entry '2' for key 'PRIMARY'
error_timestamp: 2015-11-17 11:53:10

一次呼叫完整記錄三條錯誤，業務端可據此精準修復數據。

適用場景與建議

• 批量導入/ETL：捕獲每行具體錯誤，寫入錯誤表供後續重跑。
• 異構同步：記錄衝突詳情，配合重試機制。
• 審計合規：完整保留所有違約信息。
• 注意：GET STACKED DIAGNOSTICS 僅在 DECLARE ... HANDLER 內有效；普通流程中用 GET DIAGNOSTICS（當前區，語句結束即重置）。

MySQL 5.7 的 Diagnostics Area Stack 讓錯誤處理從「拼接字串」進化為「結構化編程」，是存儲過程健壯性的關鍵升級。