-- 测试用表-- innodb 支持事务CREATE TABLE `tb_test` (`id` INT(11) NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8;
-- 测试用储存过程-- 通过插入相同的主键和插入NULL到非空字段中制造异常CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test`()BEGIN-- 如果出现执行异常则结束后继的执行，并执行begin-end中的处理DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN-- 回滚事务ROLLBACK;-- 获取错误信息GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT; -- 借“模拟”抛出异常RESIGNAL SET schema_name = 'mtt_dev', table_name = 'tb_test', message_text = @p2, mysql_errno = @p1;END;START TRANSACTION;INSERT INTO `tb_test`(`name`)VALUES ('name');INSERT INTO `tb_test`(`name`)VALUES (NULL);INSERT INTO `tb_test`(`id`,`name`)VALUES (1,'name2');INSERT INTO `tb_test`(`id`,`name`)VALUES (1,'name3'); COMMIT;END$$DELIMITER ;
-- 执行CALL sp_test();
