MySQL 8.0.24存储过程异常时事务未回滚问题咨询

这不是MySQL的BUG，是MySQL的固有机制：所有DDL语句（CREATE TABLE/ALTER TABLE/DROP TABLE等）执行前都会隐式提交当前会话中未提交的事务。
你这段存储过程里执行第一个CREATE TABLE tbl1的DDL语句时，之前start transaction开启的事务就已经被自动提交了，后续执行创建already_exists的DDL报错时，事务早就提交完成，你写的ROLLBACK自然也回滚不掉已经提交的tbl1创建操作。

因为DDL本身不支持事务回滚，所以要实现「要么所有表都创建成功要么都不创建」的效果，只能加补偿逻辑，不能依赖事务回滚，具体可以这么调整存储过程：

1. 异常捕获之后，除了回滚普通DML事务之外，手动清理已经创建成功的表
2. 可以提前校验所有要创建的表是否存在，避免执行到一半出错

修改后的存储过程示例：

delimiter $$
create procedure test_proc()
begin
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 异常触发时手动删除已经创建的tbl1表
        DROP TABLE IF EXISTS tbl1;
        ROLLBACK; -- 此处用于回滚普通DML操作
        resignal;
    END;

  -- 提前校验所有要创建的表是否存在，提前拦截报错
  IF EXISTS (SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'already_exists') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '表already_exists已存在，无法执行创建逻辑';
  END IF;

  start transaction;
    -- 此处可以放普通DML操作，这类操作支持事务回滚

    set @sql = concat('create table tbl1 (
        ROW_ID INT UNSIGNED AUTO_INCREMENT,
        PRIMARY KEY  (row_id))
        ENGINE=INNODB CHARACTER SET utf8mb4;'
    );
    prepare stmt from @sql;
    execute stmt;
    DEALLOCATE PREPARE stmt; -- 用完及时释放预处理语句

    set @sql = concat('CREATE TABLE already_exists (
        ROW_ID INT UNSIGNED AUTO_INCREMENT,
        PRIMARY KEY  (row_id))
        ENGINE=INNODB CHARACTER SET utf8mb4;
    ');
    prepare stmt from @sql;
    execute stmt;
    DEALLOCATE PREPARE stmt;

  commit;
end $$
delimiter ;

如果你的场景需要批量执行大量DDL，建议先完成所有前置条件校验，确认所有依赖都满足后再执行DDL，尽量避免执行中途出错需要回删已创建对象的情况。

内容的提问来源于stack exchange，提问作者morpheus