绕过MySQL"无法重新打开表"错误

我现在正在忙着实现一个类别的过滤器,我需要为每个"标签"生成一个INNER JOIN clausse来过滤.

问题是在一大堆SQL之后,我有一个表包含了我选择所需的所有信息,但是每次生成的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 reopen table

一些研究引导我查看这个错误报告,但MySQL的人们似乎并不关心这样的基本功能(使用多次表)不适用于临时表.我遇到了很多关于此问题的可伸缩性问题.

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

亲切的问候,克里斯

[额外]

GROUP_CONCAT答案在我的情况下不起作用,因为我的条件是按特定顺序排列的多个列,它会使OR成为我需要的AND.但是,它确实帮助我解决了早期的问题,所以现在不再需要表格,不管是不是临时表格.我们只是想对我们的问题过于笼统.过滤器的整个应用现在已经从大约一分钟恢复到大约四分之一秒.

1> 小智..：

---

一个简单的解决方案是复制临时表.如果表格相对较小,则表现良好,临时表通常就是这种情况.

---

即使临时表很大,mysql的缓存也应该帮助你.至于从一个临时表复制到另一个临时表,一个简单的"CREATE TEMPORARY TABLE tmp2 SELECT*FROM tmp1"应该这样做.

---

实际上应该是选择的答案,因为这可以解决问题,而不会四处走动.

---

有关*如何*你会复制表的任何建议？(我的意思是一种不重复查询的复制方式)

---

如果您复制临时内容，请不要忘记也创建索引，否则查询可能会很慢。

2> Bill Karwin..：

---

对,MySQL 文档说:"你不能TEMPORARY在同一个查询中多次引用一个表."

这是一个应该找到相同行的替代查询,尽管匹配行的所有条件都不在单独的列中,它们将位于以逗号分隔的列表中.

SELECT f1.baseID, GROUP_CONCAT(f1.condition)
FROM search f1
WHERE f1.condition IN (, , ... )
GROUP BY f1.baseID
HAVING COUNT(*) = ;

---

这实际上并没有解决我手边的问题,但它确实使我能够简化导致它的问题,从而消除了对临时表的需求.谢谢!

3> beeks..：

---

我通过创建一个永久的“临时”表并将SPID（很抱歉，我来自SQL Server，此后缀）添加到表名，以创建唯一的表名，从而解决了这一问题。然后创建动态SQL语句以创建查询。如果发生任何不良情况，该表将被删除并重新创建。

我希望有一个更好的选择。来吧，MySQL开发人员。自2008年以来，“错误” /“功能请求”已打开！似乎遇到的所有“错误”都在同一条船上。

select concat('ReviewLatency', CONNECTION_ID()) into @tablename;

#Drop "temporary" table if it exists
set @dsql=concat('drop table if exists ', @tablename, ';');
PREPARE QUERY1 FROM @dsql;
EXECUTE QUERY1;
DEALLOCATE PREPARE QUERY1;

#Due to MySQL bug not allowing multiple queries in DSQL, we have to break it up...
#Also due to MySQL bug, you cannot join a temporary table to itself,
#so we create a real table, but append the SPID to it for uniqueness.
set @dsql=concat('
create table ', @tablename, ' (
    `EventUID` int(11) not null,
    `EventTimestamp` datetime not null,
    `HasAudit` bit not null,
    `GroupName` varchar(255) not null,
    `UserID` int(11) not null,
    `EventAuditUID` int(11) null,
    `ReviewerName` varchar(255) null,
    index `tmp_', @tablename, '_EventUID` (`EventUID` asc),
    index `tmp_', @tablename, '_EventAuditUID` (`EventAuditUID` asc),
    index `tmp_', @tablename, '_EventUID_EventTimestamp` (`EventUID`, `EventTimestamp`)
) ENGINE=MEMORY;');
PREPARE QUERY2 FROM @dsql;
EXECUTE QUERY2;
DEALLOCATE PREPARE QUERY2;

#Insert into the "temporary" table
set @dsql=concat('
insert into ', @tablename, ' 
select e.EventUID, e.EventTimestamp, e.HasAudit, gn.GroupName, epi.UserID, eai.EventUID as `EventAuditUID`
    , concat(concat(concat(max(concat('' '', ui.UserPropertyValue)), '' (''), ut.UserName), '')'') as `ReviewerName`
from EventCore e
    inner join EventParticipantInformation epi on e.EventUID = epi.EventUID and epi.TypeClass=''FROM''
    inner join UserGroupRelation ugr on epi.UserID = ugr.UserID and e.EventTimestamp between ugr.EffectiveStartDate and ugr.EffectiveEndDate 
    inner join GroupNames gn on ugr.GroupID = gn.GroupID
    left outer join EventAuditInformation eai on e.EventUID = eai.EventUID
    left outer join UserTable ut on eai.UserID = ut.UserID
    left outer join UserInformation ui on eai.UserID = ui.UserID and ui.UserProperty=-10
    where e.EventTimestamp between @StartDate and @EndDate
        and e.SenderSID = @FirmID
    group by e.EventUID;');
PREPARE QUERY3 FROM @dsql;
EXECUTE QUERY3;
DEALLOCATE PREPARE QUERY3;

#Generate the actual query to return results. 
set @dsql=concat('
select rl1.GroupName as `Group`, coalesce(max(rl1.ReviewerName), '''') as `Reviewer(s)`, count(distinct rl1.EventUID) as `Total Events`
    , (count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) as `Unreviewed Events`
    , round(((count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) / count(distinct rl1.EventUID)) * 100, 1) as `% Unreviewed`
    , date_format(min(rl2.EventTimestamp), ''%W, %b %c %Y %r'') as `Oldest Unreviewed`
    , count(distinct rl3.EventUID) as `<=7 Days Unreviewed`
    , count(distinct rl4.EventUID) as `8-14 Days Unreviewed`
    , count(distinct rl5.EventUID) as `>14 Days Unreviewed`
from ', @tablename, ' rl1
left outer join ', @tablename, ' rl2 on rl1.EventUID = rl2.EventUID and rl2.EventAuditUID is null
left outer join ', @tablename, ' rl3 on rl1.EventUID = rl3.EventUID and rl3.EventAuditUID is null and rl1.EventTimestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) 
left outer join ', @tablename, ' rl4 on rl1.EventUID = rl4.EventUID and rl4.EventAuditUID is null and rl1.EventTimestamp between DATE_SUB(NOW(), INTERVAL 7 DAY) and DATE_SUB(NOW(), INTERVAL 14 DAY)
left outer join ', @tablename, ' rl5 on rl1.EventUID = rl5.EventUID and rl5.EventAuditUID is null and rl1.EventTimestamp < DATE_SUB(NOW(), INTERVAL 14 DAY)
group by rl1.GroupName
order by ((count(distinct rl1.EventUID) - count(distinct rl1.EventAuditUID)) / count(distinct rl1.EventUID)) * 100 desc
;');
PREPARE QUERY4 FROM @dsql;
EXECUTE QUERY4;
DEALLOCATE PREPARE QUERY4;

#Drop "temporary" table
set @dsql = concat('drop table if exists ', @tablename, ';');
PREPARE QUERY5 FROM @dsql;
EXECUTE QUERY5;
DEALLOCATE PREPARE QUERY5;

---

大叹一声。2016年7月，此临时表错误仍未修复。为了避免这个问题，我可能会想出一些与永久表名（我来自Oracle领域）连接的序列号。

---

*叹**我怀疑:(