关于 MySQL使用临时表 出现 “ERROR 1137 (HY000): Can't reopen table” 的异常 解决方法

mysql使用临时表，出现异常：

ERROR 1137 (HY000): Can't reopen table: 'tmp_query_group'

 

查了官方资料，发现：

C.5.7.2. TEMPORARY Table Problems
The following list indicates limitations on the use of TEMPORARY tables:
• A TEMPORARY table can only be of type MEMORY, MyISAM, MERGE, or InnoDB. Temporary tables are not supported for MySQL Cluster.

• You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work:
mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'
This error also occurs if you refer to a temporary table multiple times in a stored function under different aliases, even if the references occur in different statements within the function.
• The SHOW TABLES statement does not list TEMPORARY tables.
• You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead:
mysql> ALTER TABLE orig_name RENAME new_name;

 

根据经验，解决方案可考虑两个:

1.他人说使用memory 存储引擎：

create temporary table ...  engine=memory  select ....

 

但是，仍然解决不了类似下面的问题：

insert into temp_table_name (col1,col2) select col1,col2 from temp_table_name where ....

 

2.为了解决上面的问题，可以考虑使用多个临时表。

既然子查询中不能再次打开临时表，那么就使用其他临时表 先把子查询的数据存起来，然后再处理。（百试不爽）