当前位置：首页 > ai >正文

mysql存储过程中rollback、transaction、动态sql的使用

ai 2025/7/27 1:49:22

1、错误处理ROLLBACK

1、语法

DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGINROLLBACK;  -- 回滚事务SELECT 'Error occurred';
END;

2、示例

-- 创建表
CREATE TABLE employees (id INT PRIMARY KEY,name VARCHAR(100),salary DECIMAL(10, 2)
);
-- 插入准备的数据
insert into employees values(1,'张三',5400);
insert into employees values(2,'李四',5600);
insert into employees values(3,'王五',3400);
insert into employees values(4,'李明',1200);
insert into employees values(5,'王凯',6300);
-- 删除存储过程
DROP PROCEDURE IF EXISTS producer_rollback;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE producer_rollback()BEGIN-- 捕获执行过程中所有的SQL错误DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SELECT '插入失败: 数据冲突或格式错误';END;START TRANSACTION;-- 假设第三条插入违反唯一约束INSERT INTO employees (id,name,salary) VALUES (1,'Alice',6100);COMMIT;SELECT '插入成功';END $$
DELIMITER ;
-- 调用  第三条会失败，所有操作回滚
CALL producer_rollback();

使用 SELECT ‘Debug Point’; 输出调试信息

2、事务控制TRANSACTION

1、语法

START TRANSACTION;
-- 执行SQL操作
COMMIT;  -- 或 ROLLBACK;

2、示例

-- 删除存储过程
DROP PROCEDURE IF EXISTS producer_transaction;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE producer_transaction()BEGIN-- 声明变量DECLARE record_count INT;DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN-- 回滚事务ROLLBACK;END;-- 开启事务START TRANSACTION;-- 插入数据INSERT INTO employees (id,name,salary) VALUES (10,'Alice',10000);-- 查询是否插入成功SELECT count(1) INTO record_count FROM employees WHERE name = 'Alice_rollback';IF record_count > 0 THENCOMMIT; -- 提交事务ELSEROLLBACK; -- 回滚事务END IF;END $$
DELIMITER ;-- 调用
call producer_transaction();

3、动态SQL

1、语法

SET @sql = CONCAT('SELECT * FROM ', table_name);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

2、示例

-- 删除存储过程
DROP PROCEDURE IF EXISTS procedure_dynamic_sql;
-- 创建存储过程
DELIMITER $$
CREATE PROCEDURE procedure_dynamic_sql(IN id INT)
BEGINSET @sql = CONCAT('SELECT * FROM employees WHERE id = ', id);PREPARE stmt FROM @sql;-- 执行动态生成的 SQL 语句EXECUTE stmt;-- 当不再需要此预处理语句时,释放资源(DEALLOCATE PREPARE stmt 命令用于释放之前通过 PREPARE 语句准备好的预处理语句所占用的资源)DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;
-- 执行存储过程
call procedure_dynamic_sql(10);