当前位置：首页 > article >正文

MySQL记录存储过程执行的错误信息

article 2024/11/15 5:14:29

创建业务表

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 ;