Mysql 获取存储过程中的异常信息

GET DIAGNOSTICS CONDITION 1 code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;

 

delimiter$$
drop procedure if exists do_insert$$
CREATE PROCEDURE do_insert(value INT)
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE code CHAR(5) DEFAULT '00000';
  DECLARE msg TEXT;
  DECLARE rows INT;
  DECLARE result TEXT;
  -- 声明异常处理
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

  -- 执行插入语句
  INSERT INTO t1 (int_col) VALUES(value);
  -- 检查code是否改变，如果改变表示插入异常了
  select code,msg

  IF code = '00000' THEN
    GET DIAGNOSTICS rows = ROW_COUNT;
    SET result = CONCAT('insert succeeded, row count = ',rows);
  ELSE
    -- 复制异常code，异常信息
    SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
  END IF;
  -- 输入执行结果
  SELECT result;
END$$


call do_insert(1);

 

 

官方文档：https://dev.mysql.com/doc/refman/5.6/en/get-diagnostics.html

　　　　　https://dev.mysql.com/doc/refman/5.7/en/get-diagnostics.html