測試環境為 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)
沒有解決問題,試試搜尋本站其他內容