MySQL 临时表存在的限制

1. 临时表限制

使用 CREATE TEMPORARY TABLE 创建的临时表存在以下限制：

• 仅 InnoDB、MEMORY、MyISAM 和 MERGE 存储引擎支持 TEMPORARY 表；

• NDB 集群不支持临时表；

• SHOW TABLES 语句不会列出 TEMPORARY 表；

• 重命名临时表时，RENAME TABLE 不可用。需改用 ALTER TABLE：

  ALTER TABLE old_name RENAME new_name;

• 同一查询中不能多次引用同一个临时表。例如以下语句会报错：

  SELECT * FROM temp_table JOIN temp_table AS t2;

  错误信息为：

  ERROR 1137: Can't reopen table: 'temp_table'

  若查询允许使用公共表表达式（CTE）替代临时表，可通过此方式规避问题。例如以下使用临时表的操作会失败：

  CREATE TEMPORARY TABLE t SELECT 1 AS col_a, 2 AS col_b;
  SELECT * FROM t AS t1 JOIN t AS t2;

  改用 WITH 子句定义 CTE 可避免错误：

  WITH cte AS (SELECT 1 AS col_a, 2 AS col_b)
  SELECT * FROM cte AS t1 JOIN cte AS t2;

• 在存储函数中，即使通过不同别名多次引用临时表（即使引用位于不同语句中），也会触发 Can't reopen table 错误。该错误也可能出现在跨多个调用函数和被调用函数引用外部创建的临时表时；

• 若创建的临时表与已有的非临时表同名，则非临时表会被隐藏，直到临时表被删除（即使两者使用不同的存储引擎）；

• 临时表在复制场景中存在已知问题，详见复制与临时表；

2. 复制与临时表

在 MySQL 8.4 中，当 binlog_format 设置为 ROW 或 MIXED 时，仅使用临时表的语句不会在主服务器上记录日志，因此这些临时表不会被复制到从服务器。涉及临时表和非临时表混合的语句，在主服务器上只会记录非临时表的相关操作，临时表上的操作不会被记录。这意味着，从服务器上永远不会有临时表在意外关闭时丢失。有关基于行的复制和临时表的更多信息，请参阅临时表的基于行日志记录。

当 binlog_format 设置为 STATEMENT 时，临时表上的操作会在主服务器上记录并复制到从服务器，前提是涉及临时表的语句可以安全地使用基于语句的格式记录。在这种情况下，从服务器上复制的临时表可能会在意外情况下丢失。在基于 STATEMENT 的复制模式下，当服务器启用了 GTID（即 enforce_gtid_consistency 系统变量设置为 ON）时，CREATE TEMPORARY TABLE 和 DROP TEMPORARY TABLE 语句不能在事务、存储过程、函数或触发器中使用。但在这些上下文之外，当 GTID 启用且 autocommit=1 时，这些语句可以使用。

由于基于行或混合复制模式与基于语句的复制模式在临时表处理上的行为差异，如果更改适用于包含任何打开的临时表的上下文（全局或会话），则无法在运行时切换复制格式。有关更多详情，请参阅 binlog_format 选项的描述。

2.1. 使用临时表时安全关闭从服务器

在基于 STATEMENT 的复制模式下，临时表会被复制，但在以下情况下除外：您停止从服务器（不仅仅是复制线程），并且有一些已复制的临时表仍处于打开状态，用于尚未在从服务器上执行的更新操作。如果您停止从服务器，这些更新所需的临时表在从服务器重启时将不再可用。为避免这个问题，不要在从服务器有临时表打开时关闭它。请按照以下步骤操作：

1. 执行 STOP REPLICA SQL_THREAD 语句；
2. 使用 SHOW STATUS 检查 Replica_open_temp_tables 状态变量的值；
3. 如果值不为 0，则使用 START REPLICA SQL_THREAD 重启复制 SQL 线程，并稍后重复此过程；
4. 当值为 0 时，使用 mysqladmin shutdown 命令停止从服务器；

2.2. 临时表与复制选项

默认情况下，在基于 STATEMENT 的复制中，所有临时表都会被复制，无论是否存在匹配的 --replicate-do-db、--replicate-do-table 或 --replicate-wild-do-table 选项。然而，--replicate-ignore-table 和 --replicate-wild-ignore-table 选项对临时表有效。例外情况是，为了确保在会话结束时正确删除临时表，从服务器始终会复制 DROP TEMPORARY TABLE IF EXISTS 语句，即使指定表的排除规则通常适用。

在使用基于语句的复制时，建议的做法是为不想复制的临时表指定一个专用的前缀，然后使用 --replicate-wild-ignore-table 选项来匹配该前缀。例如，您可以为所有这类表命名时使用 norep 前缀（例如 norepmytable、norepyourtable 等），然后使用 --replicate-wild-ignore-table=norep% 来阻止它们被复制。

---

参考链接

• TEMPORARY Table Problems
• Replication and Temporary Tables