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

MySQL GET DIAGNOSTICS 完整語法與預存程序錯誤處理實戰

編輯：MySQL 綜合教學

MySQL GET DIAGNOSTICS 完整語法與預存程序錯誤處理實戰

GET DIAGNOSTICS 可檢查前一條 SQL 陳述式的診斷區資訊，包含陳述式層級（影響列數、條件數）與條件層級（錯誤碼、訊息、SQLSTATE）資訊。支援 CURRENT（目前診斷區）與 STACKED（條件處理器啟動時的堆疊診斷區）兩種模式，是預存程序精確錯誤處理的核心工具。

一、語法結構

GET [CURRENT | STACKED] DIAGNOSTICS
{
    @var = statement_item [, ...]
  | CONDITION condition_number @var = condition_item [, ...]
}

statement_item: NUMBER | ROW_COUNT
condition_item: CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAME

二、診斷區結構

陳述式資訊： 條件數、影響列數
條件區陣列： 每個條件包含錯誤碼、訊息、SQLSTATE 等

Statement information: row count, ...
Condition area list:
  Condition 1: error code, message, ...
  Condition 2: error code, message, ...
  ...

三、基本用法範例

1. 互動式檢查錯誤

mysql> DROP TABLE test.no_such_table;
ERROR 1051 (42S02): Unknown table 'test.no_such_table'

mysql> GET DIAGNOSTICS CONDITION 1
    -> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+------------------------------------+
| @p1   | @p2                                |
+-------+------------------------------------+
| 42S02 | Unknown table 'test.no_such_table' |
+-------+------------------------------------+

2. 取得陳述式資訊

GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

3. 取得條件資訊（需指定條件編號）

GET DIAGNOSTICS CONDITION 1 @p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

四、預存程序中使用範例

範例 1：插入操作結果判斷

CREATE PROCEDURE do_insert(value INT)
BEGIN
    DECLARE code CHAR(5) DEFAULT '00000';
    DECLARE msg TEXT;
    DECLARE rows INT;
    DECLARE result TEXT;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

    INSERT INTO t1 (int_col) VALUES (value);

    IF code = '00000' THEN
        GET DIAGNOSTICS rows = ROW_COUNT;
        SET result = CONCAT('insert succeeded, row count = ', rows);
    ELSE
        SET result = CONCAT('insert failed, error = ', code, ', message = ', msg);
    END IF;

    SELECT result;
END;

測試：

CALL do_insert(1);    -- 成功：row count = 1
CALL do_insert(NULL); -- 失敗：error = 23000, Column 'int_col' cannot be null

範例 2：STACKED 診斷區在條件處理器中的應用

CREATE PROCEDURE p()
BEGIN
    DECLARE errcount INT;
    DECLARE errno INT;
    DECLARE msg TEXT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 目前診斷區（未被修改前與 stacked 相同）
        GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'current DA before' AS op, errno, msg;

        -- 堆疊診斷區（保留觸發錯誤的原始資訊）
        GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'stacked DA before' AS op, errno, msg;

        -- 映射 NULL 插入為空字串
        INSERT INTO t1 (c1) VALUES('');

        -- 檢查目前診斷區是否已清空
        GET CURRENT DIAGNOSTICS errcount = NUMBER;
        IF errcount = 0 THEN
            SELECT 'mapped insert succeeded, current DA empty' AS op;
        ELSE
            GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
            SELECT 'current DA after' AS op, errno, msg;
        END IF;

        -- stacked 區塊始終保留原始錯誤
        GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'stacked DA after' AS op, errno, msg;
    END;

    INSERT INTO t1 (c1) VALUES('string 1');
    INSERT INTO t1 (c1) VALUES(NULL);  -- 觸發 NOT NULL 違反
END;

關鍵差異： 處理器內的陳述式會重設 CURRENT 診斷區，唯有 STACKED 區塊保留觸發錯誤的原始資訊。