关于MYSQL存储过程/函数中GET DIAGNOSTICS 异常捕捉消息返回问题说明

在MySQL存储过中处理SQLEXCEPTION的时候，如果你是使用GET CURRENT DIAGNOSTICS CONDITION 1 @SQL_MSG_CODE = RETURNED_SQLSTATE,@SQL_MSG_TEXT = MESSAGE_TEXT;的方式来读取那么每次显示的都是第一次报错的消息，那么如何显示当前最新的报错信息呢？请参照下例：

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS p;
delimiter //
CREATE PROCEDURE p ()
BEGIN
  -- Declare variables to hold diagnostics area information
  DECLARE errcount INT;
  DECLARE errno INT;
  DECLARE msg TEXT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Here the current DA is nonempty because no prior statements
    -- executing within the handler have cleared it
    GET CURRENT DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'current DA before mapped insert' AS op, errno, msg;
    GET STACKED DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'stacked DA before mapped insert' AS op, errno, msg;
 
    -- Map attempted NULL insert to empty string insert
    INSERT INTO t1 (c1) VALUES('');
 
    -- Here the current DA should be empty (if the INSERT succeeded),
    -- so check whether there are conditions before attempting to
    -- obtain condition information
    GET CURRENT DIAGNOSTICS errcount = NUMBER;
    IF errcount = 0
    THEN
      SELECT 'mapped insert succeeded, current DA is empty' AS op;
    ELSE
      GET CURRENT DIAGNOSTICS CONDITION 1
        errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
      SELECT 'current DA after mapped insert' AS op, errno, msg;
    END IF ;
    GET STACKED DIAGNOSTICS CONDITION 1
      errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
    SELECT 'stacked DA after mapped insert' AS op, errno, msg;
  END;
  INSERT INTO t1 (c1) VALUES('string 1');
  INSERT INTO t1 (c1) VALUES(NULL);
END;
//
delimiter ;
CALL p();
SELECT * FROM t1;

需要注意的是
1.GET DIAGNOSTICS语句也会清空当前诊断区中的内容，所以上述代码中把condition handler中的insert语句去掉，得到的结果也是一样的
2.如果将上述存储过程进行如下修改，也就是将3条declare变量的语句放到
declare handler中，实际的结果将取决于MySQL的版本，如果是在MySQL-5.7.2之前的版本，下述修改后并不会影响诊断区中的内容，实际结果与上述结果相同，如果实在MySQL-5.7.2及之后的版本，declare变量语句会清空当前诊断区中的内容。

CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
 
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
...

所以在需要获取诊断区中的内容时，一定要从堆栈诊断区中获取，而不是当前诊断区。