为什么只允许引用一次临时表？

Question

多次加入到一个temporary table。获取此错误：

Error Code: 1137. Can't reopen table: 'a'

我在googled上搜索了它，发现在查询中多次引用同一个temporary table是有某种限制的。有人能解释一下为什么会存在这种限制吗？

下面是一个简单查询的实例，它将导致此错误：

CREATE TEMPORARY TABLE foo
SELECT * FROM shopify_us limit 10;

SELECT *
FROM (
SELECT *
FROM shopify_us
LIMIT 10
) boo
LEFT JOIN foo a ON a.customer_id = boo.customer_id
LEFT JOIN foo b on b.customer_id = boo.customer_id

但是，如果我只是简单地删除第二个联接，我就不会再遇到错误。

Answer 1

限制是不能在同一个查询中多次引用临时表。例如，不能对临时表执行自联接.但您可以通过多个后续查询使用临时表。

下面是MySQL服务器代码中的注释，在引发此错误之前解释原因：

/*
  We're trying to use the same temporary table twice in a query.
  Right now we don't support this because a temporary table is always
  represented by only one TABLE object in THD, and it can not be
  cloned. Emit an error for an unsupported behaviour.
*/

https://github.com/mysql/mysql-server/blob/8.0/sql/sql_base.cc#L7284-L7289

THD是指会话作用域信息的数据结构。临时表的作用域为在其中创建临时表的会话。它们不是对可以由多个会话共享的表的引用。

换句话说，这只是代码中对跟踪临时表的数据结构的一个限制。

还请参阅这个bug报告的历史，可以追溯到2005年：https://bugs.mysql.com/bug.php?id=10327

在MySQL 8.0中有一个解决方案:您可以使用一个通用表表达式，而对于某些类型的使用，您可以使用临时表。