【原创】mysql 错误缓冲堆栈

什么是错误缓冲堆栈呢？ 举个很简单的例子，比如执行下面一条语句：

mysql> INSERT INTO t_datetime VALUES(2,'4','5');

ERROR 1292 (22007): Incorrect datetime value: '4' for column 'log_time' at row 1

上面1292这个代码指示的错误信息保存在哪里呢？ 就保存在错误缓冲堆栈， 在MySQL里面叫 DIAGNOSTICS AREA。 关于这个概念，一直在MySQL5.7才得到确定的更新。 

在MySQL5.5之前，想要得到这块区域的数据，就只能通过C的API来获取，从SQL层面是无法检索到的。MySQL5.5 先推出了这个概念。

在MySQL5.6发布后，不但可以检索这块区域，而且还可以重新封装，得到我们想要的数据。但是这块区域依然是只能保存一次错误代码，很容易被重置。

在MySQL5.7发布后，可以更加容易的检索这块区域，而且把这里的数据放到一个STACK里，重置的条件更加宽松。以下举例子来说明。

示例表结构如下，

CREATE TABLE `t_datetime` (

  `id` int(11) NOT NULL,

  `log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `end_time` datetime NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用来记录错误数据的日志表。

CREATE TABLE tb_log (errorno int,errortext TEXT,error_timestamp DATETIME);

在MySQL5.6环境下，我要这样写一段繁杂的代码来获取错误信息。

DELIMITER $$

USE `new_feature`$$

DROP PROCEDURE IF EXISTS `sp_do_insert`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_do_insert`(

IN f_id INT,

IN f_log_time VARCHAR(255),

IN f_end_time VARCHAR(255)

)

BEGIN

  DECLARE done1 TINYINT DEFAULT 0; -- 保存是否发生异常的布尔值。

  DECLARE i TINYINT DEFAULT 1;

  DECLARE v_errcount INT DEFAULT 0;  -- 获取一次错误数据条数

  DECLARE v_errno INT DEFAULT 0; -- 获取错误代码

  DECLARE v_msg TEXT; -- 获取错误详细信息

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION -- 定义一个异常处理块

  BEGIN

    SET done1 = 1; -- 发生异常，设置为1.

    get  diagnostics v_errcount = number;

    SET v_msg = '';

    WHILE i <= v_errcount

    DO

      GET  DIAGNOSTICS CONDITION i

        v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;

        SET @stmt = CONCAT('select ',v_errno,',"',v_msg,'","',NOW(),'" into @errno',i,',@msg',i,',@log_timestamp',i,';');

        PREPARE s1 FROM @stmt;

        EXECUTE s1;

      SET i = i + 1;

    END WHILE;

    DROP PREPARE s1;

  END;

    INSERT INTO t_datetime (id,log_time,end_time) VALUES(f_id,f_log_time,f_end_time);

    IF done1 = 1 THEN -- 把错误数据记录到表tb_log里。

      SET i = 1;

      WHILE i <= v_errcount

      DO

        SET @stmt = CONCAT('insert into tb_log ');

        SET @stmt = CONCAT(@stmt,' select @errno',i,',@msg',i,',@log_timestamp');

        PREPARE s1 FROM @stmt;

        EXECUTE s1;

        SET i = i + 1;

      END WHILE;

      DROP PREPARE s1;

    END IF;

END$$

DELIMITER ;

MySQL5.7发布后，现在可以精简我的代码了。

DELIMITER $$

USE `new_feature`$$

DROP PROCEDURE IF EXISTS `sp_do_insert`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_do_insert`(

IN f_id INT,

IN f_log_time VARCHAR(255),

IN f_end_time VARCHAR(255)

)

BEGIN

  DECLARE i TINYINT DEFAULT 1;

  DECLARE v_errcount INT DEFAULT 0; -- 获取一次错误数据条数

  DECLARE v_errno INT DEFAULT 0;  -- 获取错误代码

  DECLARE v_msg TEXT; -- 获取错误详细信息

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  -- 定义一个异常处理块

  BEGIN

    get stacked diagnostics v_errcount = number;

    WHILE i <= v_errcount

    DO

      GET stacked DIAGNOSTICS CONDITION i -- 把错误数据分别保存在变量里

        v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;

        INSERT INTO tb_log VALUES (v_errno,v_msg,NOW());

      SET i = i + 1;

    END WHILE;

  END;

    INSERT INTO t_datetime (id,log_time,end_time) VALUES(f_id,f_log_time,f_end_time);  

END$$

DELIMITER ;

现在来执行下：

mysql> call sp_do_insert(2,'4','5');

Query OK, 1 row affected (0.01 sec)

来检索表tb_log的数据。

mysql> select * from tb_log\G

*************************** 1. row ***************************

        errorno: 1265

      errortext: Data truncated for column 'log_time' at row 1

error_timestamp: 2015-11-17 11:53:10

*************************** 2. row ***************************

        errorno: 1265

      errortext: Data truncated for column 'end_time' at row 1

error_timestamp: 2015-11-17 11:53:10

*************************** 3. row ***************************

        errorno: 1062

      errortext: Duplicate entry '2' for key 'PRIMARY'

error_timestamp: 2015-11-17 11:53:10

3 rows in set (0.00 sec)

总结下， 如果先用到DIAGNOSTICS AREA, 最好是在存储过程里面写代码封装SQL。 

©著作权归作者所有：来自51CTO博客作者david_yeung的原创作品，如需转载，请注明出处，否则将追究法律责任

MySQL存储函数SQL语句与特殊技巧