mysql - 错误 1137 : Can't reopen table when joining a temporary table

Question

我有一个存储过程:

DROP PROCEDURE IF EXISTS dijResolve; 
DELIMITER | 
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
BEGIN 
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
  CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
  CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;

  -- null out path info in the nodes table 
  UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
  -- find nodeIDs referenced by input params 
  SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
  IF vFromNodeID IS NULL THEN 
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
  ELSE 
    BEGIN 
      -- start at src node 
      SET vNodeID = vFromNodeID; 
      SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
      IF vToNodeID IS NULL THEN 
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
      ELSE 
        BEGIN 
          -- calculate path costs till all are done 
          UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
          WHILE vNodeID IS NOT NULL DO 
            BEGIN 
              UPDATE  
                new_dijnodes AS src 
                JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
                JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
              SET dest.Cost = CASE 
                                WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                                WHEN src.Cost + Paths.Cost < dest.Cost THEN src.Cost + Paths.Cost 
                                ELSE dest.Cost 
                              END, 
                  dest.PathID = Paths.PathID 
              WHERE  
                src.NodeID = vNodeID 
                AND (dest.Cost IS NULL OR src.Cost + Paths.Cost < dest.Cost) 
                AND dest.Calculated = 0; 

              UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 

              SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                              WHERE Calculated = 0 AND Cost IS NOT NULL 
                              ORDER BY Cost LIMIT 1 
                            ); 
            END; 
          END WHILE; 
        END; 
      END IF; 
    END; 
  END IF; 
  IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
    -- problem,  cannot proceed 
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
  ELSE 
    BEGIN 
      -- write itinerary to map table 
      DROP TEMPORARY TABLE IF EXISTS map; 
      CREATE TEMPORARY TABLE map ( 
        RowID INT PRIMARY KEY AUTO_INCREMENT, 
        FromNodeName VARCHAR(20), 
        ToNodeName VARCHAR(20), 
        Cost INT 
      ) ENGINE=MEMORY; 
      WHILE vFromNodeID <> vToNodeID DO 
        BEGIN 
          SELECT  
            src.NodeName,dest.NodeName,dest.Cost,dest.PathID 
            INTO vFromNodeName, vToNodeName, vCost, vPathID 
          FROM  
            new_dijnodes AS dest 
            JOIN new_dijpaths AS Paths ON Paths.PathID = dest.PathID 
            JOIN new_dijnodes AS src ON src.NodeID = Paths.FromNodeID 
          WHERE dest.NodeID = vToNodeID; 

          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 

          SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
        END; 
      END WHILE; 
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
      DROP TEMPORARY TABLE Map; 
    END; 
  END IF; 
END; 
| 
DELIMITER ;

这个函数取自这个站点 http://www.artfulsoftware.com/infotree/qrytip.php?id=766

好吧，我已将其更改为能够对临时表进行计算，因此无需在表中保存数据。但是我遇到了一个问题，Mysql不允许调用其他名称的临时表。所以在上面的代码中我会遇到一个错误

#1137 - Can't reopen table: 'src'

上面的错误来自这个查询

      UPDATE  
        new_dijnodes AS src 
        JOIN new_dijpaths AS paths ON paths.FromNodeID = src.NodeID 
        JOIN new_dijnodes AS dest ON dest.NodeID = Paths.ToNodeID 
      SET dest.Cost = CASE 
                        WHEN dest.Cost IS NULL THEN src.Cost + Paths.Cost 
                        WHEN src.Cost + Paths.Cost < dest.Cost THEN src.Cost + Paths.Cost 
                        ELSE dest.Cost 
                      END, 
          dest.PathID = Paths.PathID 
      WHERE  
        src.NodeID = vNodeID 
        AND (dest.Cost IS NULL OR src.Cost + Paths.Cost < dest.Cost) 
        AND dest.Calculated = 0; 

这里是 http://sqlfiddle.com/#!9/bc5a01c我的表的数据如您所见，上面的查询以 src 和 dest 的形式连接同一个表并更新它们的字段。我试图创建另一个 new_dijnodes 但我无法让它工作，这是我的尝试

DROP PROCEDURE IF EXISTS dijResolve; 
DELIMITER | 
CREATE PROCEDURE dijResolve( pFromNodeName VARCHAR(20), pToNodeName VARCHAR(20) ) 
BEGIN 
  DECLARE vFromNodeID, vToNodeID, vNodeID, vCost, vPathID INT; 
  DECLARE vFromNodeName, vToNodeName VARCHAR(20); 
  DROP TEMPORARY TABLE IF EXISTS new_dijnodes; 
  DROP TEMPORARY TABLE IF EXISTS new_dijpaths; 

  CREATE TEMPORARY TABLE new_dijnodes engine=memory AS SELECT * FROM dijnodes;
  CREATE TEMPORARY TABLE new_dijpaths AS SELECT * FROM dijpaths;

  -- null out path info in the nodes table 
  UPDATE new_dijnodes SET PathID = NULL,Cost = NULL,Calculated = 0; 
  -- find nodeIDs referenced by input params 
  SET vFromNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pFromNodeName ); 
  IF vFromNodeID IS NULL THEN 
    SELECT CONCAT('From node name ', pFromNodeName, ' not found.' );  
  ELSE 
    BEGIN 
      -- start at src node 
      SET vNodeID = vFromNodeID; 
      SET vToNodeID = ( SELECT NodeID FROM new_dijnodes WHERE NodeName = pToNodeName ); 
      IF vToNodeID IS NULL THEN 
        SELECT CONCAT('From node name ', pToNodeName, ' not found.' ); 
      ELSE 
        BEGIN 
          -- calculate path costs till all are done 
          UPDATE new_dijnodes SET Cost=0 WHERE NodeID = vFromNodeID; 
          WHILE vNodeID IS NOT NULL DO 
            BEGIN 
            DROP TEMPORARY TABLE IF EXISTS new_dijnodes_dst; 
            CREATE TEMPORARY TABLE new_dijnodes_dst AS SELECT * FROM new_dijnodes;

              UPDATE  
                new_dijnodes  
                JOIN new_dijpaths  ON new_dijpaths.FromNodeID = new_dijnodes.NodeID 
                JOIN new_dijnodes_dst ON new_dijnodes_dst.NodeID = new_dijpaths.ToNodeID 
              SET new_dijnodes_dst.Cost = CASE 
                                WHEN new_dijnodes_dst.Cost IS NULL THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                WHEN new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost THEN new_dijnodes.Cost + new_dijpaths.Cost 
                                ELSE new_dijnodes_dst.Cost 
                              END, 
                  new_dijnodes_dst.PathID = new_dijpaths.PathID 
              WHERE  
                new_dijnodes.NodeID = vNodeID 
                AND (new_dijnodes_dst.Cost IS NULL OR new_dijnodes.Cost + new_dijpaths.Cost < new_dijnodes_dst.Cost) 
                AND new_dijnodes_dst.Calculated = 0; 

              UPDATE new_dijnodes SET Calculated = 1 WHERE NodeID = vNodeID; 

              SET vNodeID = ( SELECT nodeID FROM new_dijnodes 
                              WHERE Calculated = 0 AND Cost IS NOT NULL 
                              ORDER BY Cost LIMIT 1 
                            ); 
            END; 
          END WHILE; 
        END; 
      END IF; 
    END; 
  END IF; 
  IF EXISTS( SELECT 1 FROM new_dijnodes WHERE NodeID = vToNodeID AND Cost IS NULL ) THEN 
    -- problem,  cannot proceed 
    SELECT CONCAT( 'Node ',vNodeID, ' missed.' ); 
  ELSE 
    BEGIN 
      -- write itinerary to map table 
      DROP TEMPORARY TABLE IF EXISTS map; 
      CREATE TEMPORARY TABLE map ( 
        RowID INT PRIMARY KEY AUTO_INCREMENT, 
        FromNodeName VARCHAR(20), 
        ToNodeName VARCHAR(20), 
        Cost INT 
      ) ENGINE=MEMORY; 
      WHILE vFromNodeID <> vToNodeID DO 
        BEGIN 
        DROP TEMPORARY TABLE IF EXISTS new_dijnodes_src; 
        CREATE TEMPORARY TABLE new_dijnodes_src AS SELECT * FROM new_dijnodes;

          SELECT  
            new_dijnodes_src.NodeName,new_dijnodes.NodeName,new_dijnodes.Cost,new_dijnodes.PathID 
            INTO vFromNodeName, vToNodeName, vCost, vPathID 
          FROM  
            new_dijnodes 
            JOIN new_dijpaths  ON new_dijpaths.PathID = new_dijnodes.PathID 
            JOIN new_dijnodes_src  ON new_dijnodes_src.NodeID = new_dijpaths.FromNodeID 
          WHERE new_dijnodes.NodeID = vToNodeID; 

          INSERT INTO Map(FromNodeName,ToNodeName,Cost) VALUES(vFromNodeName,vToNodeName,vCost); 

          SET vToNodeID = (SELECT FromNodeID FROM new_dijpaths WHERE PathID = vPathID); 
        END; 
      END WHILE; 
      SELECT FromNodeName,ToNodeName,Cost FROM Map ORDER BY RowID DESC; 
      DROP TEMPORARY TABLE Map; 
    END; 
  END IF; 
END; 
| 
DELIMITER ;

但它总是返回 SELECT CONCAT( 'Node ',vNodeID, 'missed.' ); 因为它更新 new_dijnodes_dst 这是表重复。

我也不能制作任何真实的表格，因为这个过程对每个用户都是唯一的，而且它的处理对于多用户来说并不容易。有什么解决方案可以解决这个问题？谢谢

Answer 1

MySQL doc建议

You cannot refer to a TEMPORARY table more than once in the same query

请引用this线。大多数实用的解决方案似乎是

1. 用永久表替换临时表
2. 用临时表后面的子查询替换临时表的后续调用
3. 复制临时表
4. 寻找解决方法自行加入

由于您的特定问题是使用自连接进行更新并且您不想要永久表，因此我建议制作重复的临时表是最合适的选择。

I tried to create another new_dijnodes but I could not make it work

能否请您分享一下您在这方面遇到的问题？