创建一个带事务和错误日志的存储过程

首先创建日志表用于记录存储过程执行错误信息

CREATE TABLE LOG_PROCEDURE
(
   ID                   BIGINT NOT NULL AUTO_INCREMENT,
   PRCD_NAME            VARCHAR(64) NOT NULL,
   MYSQL_ERRNO          VARCHAR(8),
   RETURN_STATE         VARCHAR(8),
   MESSAGE              TEXT,
   CREATE_TIMESTAMP     DATETIME DEFAULT CURRENT_TIMESTAMP,
   MODIFY_TIMESTAMP     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (ID)
);

然后创建一张测试数据表，用于测试存储过程

CREATE TABLE DATA_TEST
(
   ID                   BIGINT NOT NULL AUTO_INCREMENT,
   COL_A                VARCHAR(64),
   PRIMARY KEY (ID)
);

创建存储过程，在存储过程事务中和事务后分别往测试数据表里存入一条记录，事务中另外试图往一张不存在的表里存入一条记录。

DELIMITER $$
DROP PROCEDURE IF EXISTS PRCD_TEST$$
CREATE PROCEDURE PRCD_TEST (
	/** 定义IN,OUT */
)
BEGIN
	/** 定义变量 */

	/** 定义错误捕获 */
	DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
	BEGIN
		/** 获取错误内容 */
		GET DIAGNOSTICS CONDITION 1 @p1 = MYSQL_ERRNO, @p2 = RETURNED_SQLSTATE, @p3 = MESSAGE_TEXT;
		ROLLBACK;

		/** 错误信息存入日志表 */
		INSERT INTO LOG_PROCEDURE(PRCD_NAME, MYSQL_ERRNO, RETURN_STATE, MESSAGE)
			VALUES ('PRCD_TEST', @p1, @p2, @p3);

		/** 失败后 */
		INSERT INTO DATA_TEST(COL_A) VALUE('INSERT_FAILED');
	END;

	/** 开启事务 */
	START TRANSACTION;

	/** 执行内容 */
	INSERT INTO DATA_TEST(COL_A) VALUE('TEST_01');
	INSERT INTO NOT_EXIST_TABLE(ID) VALUE('ERROR');
	INSERT INTO DATA_TEST(COL_A) VALUE('TEST_02');

	/** 提交事务 */
	COMMIT;

	/** 成功后 */
	INSERT INTO DATA_TEST(COL_A) VALUE('INSERT_SUCCESS');
END$$
DELIMITER ;

调用存储过程

CALL PRCD_TEST();

执行结果

错误日志表 LOG_PROCEDURE 里的记录

ID	PRCD_NAME	MYSQL_ERRNO	RETURN_STATE	MESSAGE
1	PRCD_TEST	1146	42S02	Table ‘schema.not_exist_table’ doesn’t exist

再看看测试数据表

ID	COL_A
2	INSERT_FAILED

简单说明一下执行过程：

1. 开启事务
2. 往测试数据表里存入TEST_01（ID：1）
3. 往NOT_EXIST_TABLE存入记录（异常）
4. SQLEXCEPTION被捕获，获取错误信息
5. 事务回滚
6. 将获取到的错误信息存入错误日志表
7. 往测试数据表里存入INSERT_FAILED（ID：2）

所以测试数据表里的记录ID是2而不是1