不能把所有的东西都放在临时的桌子上吗?

I'm trying to run the following simple test- creating a temp table, and then UNIONing two different selections:

我尝试运行以下简单的测试—创建一个临时表，然后将两个不同的选择组合在一起:

CREATE TEMPORARY TABLE tmp 
SELECT * FROM people;

SELECT * FROM tmp
UNION ALL
SELECT * FROM tmp;

But get a #1137 - Can't reopen table: 'tmp'

但是得到#1137 -不能重新打开表:'tmp'

I thought temp tables were supposed to last the session. What's the problem here?

我认为临时表应该是最后一次。这里的问题是什么?

3 个解决方案

#1

This error indicates that the way in which Mysql tables manages the temporary tables has been changed which in turn affects the joins, unions as well as subqueries. To fix mysql error can’t reopen table, try out the following solution:

这个错误表明Mysql表管理临时表的方式已经发生了变化，这反过来又影响到连接、结合和子查询。要修复mysql错误不能重新打开表，请尝试以下解决方案:

mysql> CREATE TEMPORARY TABLE tmp_journals_2 LIKE tmp_journals;

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tmp_journals_2 SELECT * FROM tmp_journals;

After this you can perform the union operation.

在此之后，您可以执行union操作。

http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html

http://www.mysqlrepair.org/mysqlrepair/cant-reopen-table.php

#2

As documented under TEMPORARY Table Problems:

如临时表问题所记载:

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.

如果在不同别名下的存储函数中多次引用一个临时表，即使引用在函数内的不同语句中出现，也会出现此错误。

#3

Figured it out thanks to sshekar's answer- the solution in this case would be

多亏了sshekar的回答，这个问题的解决方案就是。

Create an empty temp table
创建一个空的临时表。
Insert the results we want to UNION into the table separately
将我们想要合并的结果分别插入到表中。
Query the temp table
查询临时表

SQL:

SQL:

CREATE TEMPORARY TABLE tmp LIKE people;

INSERT INTO tmp SELECT * FROM people; /* First half of UNION */
INSERT INTO tmp SELECT * FROM people; /* Second half of UNION */
SELECT * FROM tmp;

(See Using MySQL Temporary Tables to save your brain)

(参见使用MySQL临时表来保存您的大脑)

#1