创建业务表

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)
beginDECLARE errcode CHAR(5) DEFAULT '00000';DECLARE errmsg TEXT;DECLARE rowcount INT DEFAULT '0';DECLARE result TEXT;-- 声明异常处理DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONBEGINGET DIAGNOSTICS CONDITION 1-- 获取异常code,异常信息errcode = RETURNED_SQLSTATE, errmsg = MESSAGE_TEXT;END;INSERT INTO tb_user (`name`, age) VALUES(NAME, age);-- 检查code是否改变，如果改变表示插入异常了IF errcode = '00000' THENGET DIAGNOSTICS rowcount = ROW_COUNT;SET result = CONCAT('succeeded, row count = ',rowcount);ELSESET 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