MySQL 5.7只读事务中创建临时表的兼容方案求助

问题概述 #

我正在协助将基于MySQL 5.5的应用迁移至MySQL 5.7，原应用通过把约30%的业务逻辑封装在只读事务中实现了性能优化，但迁移后频繁触发：

Error Code: 1792 Cannot execute statement in a READ ONLY transaction.

核心痛点是：部分只读事务会调用包含创建临时表逻辑的存储过程，大规模改写应用代码的成本极高。测试用例如下：

START TRANSACTION READ only;
CREATE TEMPORARY TABLE IF NOT EXISTS table1 (account_id INT(10) UNSIGNED);
ROLLBACK;

根因分析 #

从MySQL 5.6开始，官方对只读事务增加了明确限制：

"In read-only mode, it remains possible to change tables created with the TEMPORARY keyword using DML statements. Changes made with DDL statements are not permitted, just as with permanent tables."

简单说就是：只读事务里允许对已存在的临时表执行DML，但绝对禁止创建临时表这类DDL操作——这和MySQL 5.5的宽松行为完全不同，也是迁移后报错的直接原因。

可行的Workaround方案 #

1. 延迟设置事务只读属性（优先推荐） #

不在事务启动时直接声明READ ONLY，而是先在普通事务中完成临时表的创建，再切换为只读模式：

START TRANSACTION;
-- 先在普通事务里完成临时表DDL
CREATE TEMPORARY TABLE IF NOT EXISTS table1 (account_id INT(10) UNSIGNED);
-- 切换为只读事务，保留性能优势
SET TRANSACTION READ ONLY;
-- 执行你的只读业务逻辑，比如查询、统计等
SELECT COUNT(*) FROM table1;
ROLLBACK;

这个方案的优势是：完全不需要大规模改写业务逻辑，只是调整事务属性的设置时机，既兼容了临时表创建需求，又保留了只读事务的性能优化效果。

2. 会话级预创建临时表（备选） #

如果临时表的生命周期可以覆盖整个数据库会话（而非单个事务），可以在应用建立数据库连接后、执行业务逻辑前预先创建临时表。这样后续的只读事务就可以直接使用该临时表做DML操作，避免在只读事务内执行DDL。不过你提到提前创建不可行，这个方案可以作为特定场景的补充。

3. 改造存储过程的事务适配逻辑 #

如果创建临时表的逻辑封装在存储过程中，可以在存储过程内部增加事务状态判断，动态切换事务模式：

DELIMITER //
CREATE PROCEDURE init_temp_table()
BEGIN
    DECLARE current_read_only BOOLEAN;
    -- 获取当前事务的只读状态
    SELECT @@transaction_read_only INTO current_read_only;
    
    IF current_read_only THEN
        -- 临时切换为读写事务创建表，再切回只读
        SET TRANSACTION READ WRITE;
        CREATE TEMPORARY TABLE IF NOT EXISTS table1 (account_id INT(10) UNSIGNED);
        SET TRANSACTION READ ONLY;
    ELSE
        -- 普通事务直接创建
        CREATE TEMPORARY TABLE IF NOT EXISTS table1 (account_id INT(10) UNSIGNED);
    END IF;
END //
DELIMITER ;

注意：这个方案需要确保存储过程有足够的权限，且切换事务模式不会破坏业务逻辑的数据一致性。

4. 系统变量调整（不推荐生产环境） #

MySQL 5.7没有直接关闭该限制的配置项，虽然可以通过调整会话级的transaction_read_only变量绕过限制，但这会完全丧失只读事务的安全性和性能优势，只建议在测试环境临时验证使用。

验证结果 #

使用第一个方案的测试代码可以正常执行，不会触发1792错误，同时保留了只读事务的性能特性。

内容的提问来源于stack exchange，提问作者jarm.dahl