MySQL如何将存储过程执行错误记录到表中

如何将存储过程执行错误记录到表中以便调试？

简单的方法是创建一个表来存储存储过程错误，如下所示

1.创建日志表以捕获程序错误:

<code >mysql> create table procedure_log(
id serial,
procedure_Name varchar(64),
connection_Id int(11),
user_Executed varchar(100),
returned_Error text,
date_Occurred timestamp
) ENGINE=INNODB;
Query OK, 0 rows affected (0.18 sec)


mysql>CREATE TABLE `andy` (
  `id` int NOT NULL,
  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2.编写一个示例存储过程，在遇到SQLEXCEPTION时将错误插入到表中:

<code >mysql> DELIMITER $$

DROP PROCEDURE IF EXISTS test.test_procedure $$

CREATE PROCEDURE test_procedure(tablename varchar(25))

BEGIN

/* This is the handler part to capture the exception and log it into a table. */

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN

GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
SET @full_error = CONCAT("ERROR ", @errno, " (", @sqlstate, "): ", @text);

INSERT INTO test.procedure_log(procedure_Name, connection_Id, user_Executed, returned_Error, date_Occurred)
SELECT 'test_procedure', CONNECTION_ID(), CURRENT_USER(), @full_error, NOW();

SELECT "Stored procedure failed with errors" as Errmsg; /* This error will be returned to application */
END;

/* This part will just select the data from the tablename that is provided as a input for this procedure */

SET @test=CONCAT('Select * from ',tablename,' ;');
PREPARE STMT FROM @test;
EXECUTE STMT;

END $$

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

3.使用当前数据库中存在的表名调用过程:

<code >mysql> call test_procedure('andy');
+------+
| id   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

这只是从现有的表中选择行并打印出来。它不打印procedure_log表中的任何条目，因为该过程没有引发异常。

<code >mysql> select * from procedure_log;
Empty set (0.01 sec)

4.使用不存在的表名调用过程:

<code >mysql> call test_procedure('t1');
+---------------------------------------+
| Errmsg                                |
+---------------------------------------+
| Stored procedure failed with errors   |
+---------------------------------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

选择程序日志表中的条目时，它包含以下内容:

<code >(Tue Sep 12 11:21:14 2023)[root@GreatSQL][test]>select * from procedure_log;
+----+----------------+---------------+---------------+---------------------------------------------------+---------------------+
| id | procedure_Name | connection_Id | user_Executed | returned_Error                                    | date_Occurred       |
+----+----------------+---------------+---------------+---------------------------------------------------+---------------------+
|  1 | test_procedure |       1976419 | root@%        | ERROR 1146 (42S02): Table 'test.t1' doesn't exist | 2023-09-12 11:19:42 |
+----+----------------+---------------+---------------+---------------------------------------------------+---------------------+
1 row in set (0.00 sec)

除了测试用例中提到的SQLEXCEPTION之外，还有处理SQL语句返回的SQLWARNINGS和其他消息的选项。