SQL – GET DIAGNOSTICS

測試環境為 CentOS 8 x86_64 (虛擬機)

執行 SQL 指令時,錯誤會直接顯示出來.

MariaDB [(none)]> INSERT INTO test VALUE(100);
ERROR 1046 (3D000): No database selected

想要保留這錯誤訊息可以透過 GET DIAGNOSTICS 來處理,語法如下.

GET [CURRENT] DIAGNOSTICS
{
    statement_property
    [, statement_property] ... 
  | CONDITION condition_number
    condition_property
    [, condition_property] ...
}

statement_property:
    variable = statement_property_name

condition_property:
    variable  = condition_property_name

statement_property_name:
    NUMBER
  | ROW_COUNT

condition_property_name:
    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

GET DIAGNOSTICS

參考範例 – https://mariadb.com/kb/en/get-diagnostics/

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

故意產生一個錯誤.

MariaDB [(none)]> INSERT INTO test VALUE(100);
ERROR 1046 (3D000): No database selected

第一步就是透過指令 GET DIAGNOSTICS ,參數 statement property 中的 NUMBER 來檢視這一次產生的錯誤有幾筆(上面範例只有一筆).

MariaDB [(none)]> GET DIAGNOSTICS @num_conditions = NUMBER;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @num_conditions;
+-----------------+
| @num_conditions |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.000 sec)

這一次產生的錯誤只有一筆,指定參數 CONDITION 為 1 ( 錯誤的第一筆,如果有第二筆就改成 CONDITION 2 ),並把參數 condition property 中的 RETURNED_SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT 資料儲存起來.

MariaDB [(none)]> GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @sqlstate, @errno, @text;
+-----------+--------+----------------------+
| @sqlstate | @errno | @text                |
+-----------+--------+----------------------+
| 3D000     |   1046 | No database selected |
+-----------+--------+----------------------+
1 row in set (0.000 sec)

PROCEDURE + GET DIAGNOSTICS

通常使用 PROCEDURE 會加入 GET DIAGNOSTICS 來確保資料處理沒有發生問題,來看下面的參考範例 – https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html

先建立測試用的資料表.

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE t1 (int_col INT, PRIMARY KEY (int_col));
Query OK, 0 rows affected (0.013 sec)

建立 PROCEDURE .

DROP PROCEDURE IF EXISTS do_insert;
 
DELIMITER //

CREATE PROCEDURE do_insert(value INT)
BEGIN
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE nrows 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 nrows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',nrows);
  ELSE
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  SELECT result;
END;
//
 
DELIMITER ;

說明:

• DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  這是關於 Store Procedure 的例外處理 ( DECLARE Error Handler ) – https://benjr.tw/103022 .概念很簡單就是在 Store Procedure 裡面發生錯誤時需要怎麼來處裡.
  這邊定義當發生 SQLEXCEPTION (所有 SQLSTATE 不屬於 00 , 01 , 02 開頭的錯誤, MariaDB Error Codes 請參考 – https://mariadb.com/kb/en/mariadb-error-codes/ ) 時,就開始執行下面的 BEGIN 到 END 的 SQL 指令.

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
  

  開始 HANDLER 要執行的 SQL 指令.

      BEGIN
  

  透過 GET DIAGNOSTICS 把 CONDITION 1 的 RETURNED_SQLSTATE, MESSAGE_TEXT 訊息記錄到 code 與 msg 變數.

        GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
  

  結束 HANDLER 要執行的 SQL 指令.

      END;
  

• INSERT INTO t1 (int_col) VALUES(value);
  INSERT 一筆資料到 t1 資料表.

    INSERT INTO t1 (int_col) VALUES(value);
  

• IF ELSE END IF;
  前面已經透過 GET DIAGNOSTICS 把 RETURNED_SQLSTATE, MESSAGE_TEXT 訊息記錄到 code 與 msg 變數.這邊就先檢視 code 的值是否為 00000 (代表成功執行)並顯示其成功訊息,非則把其相對應錯誤訊息顯示出來.

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

執行結果,一次輸入正確值 (數字 1),另外一次故意輸入 NULL.

MariaDB [testdb]> CALL do_insert(1);
+---------------------------------+
| result                          |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+
1 row in set (0.003 sec)

Query OK, 1 row affected (0.003 sec)

MariaDB [testdb]> CALL do_insert(NULL);
+-------------------------------------------------------------------------+
| result                                                                  |
+-------------------------------------------------------------------------+
| insert failed, error = 23000, message = Column 'int_col' cannot be null |
+-------------------------------------------------------------------------+
1 row in set (0.001 sec)

Query OK, 0 rows affected (0.001 sec)