MySQL记录存储过程执行的错误信息
创建业务表
CREATE TABLE `tb_user` (
`id` int NOT NULL primary key,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL
);
创建错误日志表
CREATE TABLE error_log (
error_id INT AUTO_INCREMENT PRIMARY KEY,
error_code char(5),
error_message text,
error_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
创建存储过程，如果运行报错，插入到error_log
DELIMITER $$
drop PROCEDURE if exists insert_user;
CREATE PROCEDURE insert_user(IN name VARCHAR(255), IN age int)
begin
DECLARE errcode CHAR(5) DEFAULT '00000';
DECLARE errmsg TEXT;
DECLARE rowcount INT DEFAULT '0';
DECLARE result TEXT;
-- 声明异常处理
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
-- 获取异常code,异常信息
errcode = RETURNED_SQLSTATE, errmsg = MESSAGE_TEXT;
END;
INSERT INTO tb_user (`name`, age) VALUES(NAME, age);
-- 检查code是否改变，如果改变表示插入异常了
IF errcode = '00000' THEN
GET DIAGNOSTICS rowcount = ROW_COUNT;
SET result = CONCAT('succeeded, row count = ',rowcount);
ELSE
SET result = CONCAT('failed, error = ',errcode,', message = ',errmsg);
END IF;
-- 记录到错误日志表
INSERT INTO error_log(error_code, error_message)VALUES(errcode, result);
-- 输出执行结果
select result;
end $$
DELIMITER ;
调用测试
CALL insert_user('John Doe', 20);
查询执行结果
select * from error_log;
参考mysql官方文档：https://dev.mysql.com/doc/refman/8.4/en/get-diagnostics.html