绕过 MySQL“无法重新打开表"错误 [英] Getting around MySQL "Can't reopen table" error

问题描述

我目前正忙于实现一种过滤器，我需要为每个要过滤的标签"生成一个 INNER JOIN 子句.

I'm currently busy implementing a filter of sorts for which I need to generate an INNER JOIN clausse for every "tag" to filter on.

问题是，在执行了一大堆 SQL 之后，我有一个包含我进行选择所需的所有信息的表，但是对于每个生成的 INNER JOIN 我都需要再次使用它

The problem is that after a whole bunch of SQL, I have a table that contains all the information I need to make my selection, but I need it again for every generated INNER JOIN

这基本上看起来像:

SELECT
    *
FROM search
INNER JOIN search f1 ON f1.baseID = search.baseID AND f1.condition = condition1
INNER JOIN search f2 ON f2.baseID = search.baseID AND f2.condition = condition2
...
INNER JOIN search fN ON fN.baseID = search.baseID AND fN.condition = conditionN

这可行，但我更希望搜索"表是临时表(如果它不是普通表，它可以小几个数量级)但这给了我一个非常烦人的错误:Can't 重新打开表格

This works but I would much prefer the "search" table to be temporary (it can be several orders of magnitude smaller if it isn't a normal table) but that gives me a very annoying error: Can't reopen table

一些研究让我找到了这个错误报告，但 MySQL 的人似乎并不关心这样一个基本功能(多次使用一个表)不适用于临时表.我在这个问题上遇到了很多可扩展性问题.

Some research leads me to this bug report but the folks over at MySQL don't seem to care that such a basic feature (using a table more than once) does not work with temporary tables. I'm running into a lot of scalability problems with this issue.

是否有任何可行的解决方法不需要我管理潜在的大量临时但非常真实的表或让我维护一个包含所有数据的大表?

Is there any viable workaround that does not require me to manage potentially lots of temporary but very real tables or make me maintain a huge table with all the data in it?

亲切的问候，克里斯

[附加]

GROUP_CONCAT 答案在我的情况下不起作用，因为我的条件是按特定顺序排列的多列，它会使 OR 脱离我需要的 AND.但是，它确实帮助我解决了一个较早的问题，因此现在不再需要表，无论是否为临时表.我们只是认为我们的问题太笼统了.过滤器的整个应用现在已经从大约一分钟恢复到不到四分之一秒.

The GROUP_CONCAT answer does not work in my situation because my conditions are multiple columns in specific order, it would make ORs out of what I need to be ANDs. However, It did help me solve an earlier problem so now the table, temp or not, is no longer required. We were just thinking too generic for our problem. The entire application of filters has now been brought back from around a minute to well under a quarter of a second.

推荐答案

如果切换到 MariaDB(MySQL 的一个分支)是可行的——这个烦恼在 10.2.1 版本中得到修复:https://jira.mariadb.org/browse/MDEV-5535.

If switching to MariaDB (a fork of MySQL) is feasible -- this annoyance is fixed there as of version 10.2.1: https://jira.mariadb.org/browse/MDEV-5535.

这篇关于绕过 MySQL“无法重新打开表"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！