代码之家  ›  专栏  ›  技术社区  ›  Kris

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

  •  72
  • Kris  · 技术社区  · 16 年前

    我目前正忙于实现一个过滤器,我需要为每个要过滤的“标记”生成一个内部连接子句。

    问题是,经过一大堆SQL之后,我有了一个表,其中包含了进行选择所需的所有信息,但对于生成的每个内部联接,我都需要它

    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

    一些研究让我想到 this bug report 但是MySQL的工作人员似乎并不关心这样一个基本特性(多次使用一个表)不适用于临时表。我在这个问题上遇到了很多可伸缩性问题。

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

    6 回复  |  直到 16 年前
        1
  •  138
  •   Kevin Kalitowski    8 年前

        2
  •  51
  •   Bill Karwin    16 年前

    对,MySQL docs 说:“你不能引用 TEMPORARY 表在同一查询中多次出现。”

    这里有一个替代查询,它应该查找相同的行,尽管匹配行的所有条件不会在单独的列中,但它们将在逗号分隔的列表中。

    SELECT f1.baseID, GROUP_CONCAT(f1.condition)
    FROM search f1
    WHERE f1.condition IN (<condition1>, <condition2>, ... <conditionN>)
    GROUP BY f1.baseID
    HAVING COUNT(*) = <N>;
    
        3
  •  7
  •   beeks    11 年前

    我希望有更好的选择。来吧,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;
    
        4
  •  3
  •   MarkR    16 年前

    就我个人而言,我会把它变成一张永久的桌子。您可能希望为这些表创建一个单独的数据库(可能它们需要唯一的名称,因为许多查询可以一次完成),同时允许合理地设置权限(您可以设置数据库的权限;您不能设置表通配符的权限)。

        5
  •  2
  •   krlmlr    4 年前

    如果切换到MariaDB(MySQL的一个分支)是可行的,那么从版本10.2.1开始,这个麻烦就已经解决了: https://jira.mariadb.org/browse/MDEV-5535

        6
  •  1
  •   Inc33    6 年前

    您可以通过创建一个永久表(稍后将其删除)或使用相同的数据创建两个单独的临时表来解决此问题

        7
  •  1
  •   Tanner Clark    5 年前

    enter image description here

    下面是关于这个问题的MYSQL文档。我使用重复的临时表,如上面的一些答案,但是,您可能有一个CTE是适当的情况!

    https://dev.mysql.com/doc/refman/8.0/en/temporary-table-problems.html

        8
  •  0
  •   Cary Cary    15 年前

    我能够将查询更改为永久表,这为我修复了它。(已更改MicroStrategy中的VLDB设置,临时表类型)。