mySQL传递闭包表 [英] mySQL transitive closure table

问题描述

我在SQL Server中使用了一些代码，以便从另一个具有直接父/子关系的表中生成一个关闭表，我可以对此执行非常简单的查询以确定沿袭.现在我需要在mySQL中执行所有这些操作，但是在递归查询以生成闭合表时遇到了麻烦...

I have some code I've been using in SQL Server to generate a closure table from another table that has just the direct parent/child relationships, I can run very simple queries against this to determine lineage. Now I am needing to do all this in mySQL, but I am having trouble with the recursive querying to generate the closure table...

我原来的SQL Server查询是

My original SQL server query is

WHILE @@ROWCOUNT>0
INSERT INTO [ClosureTable] ([Ancestor], [Descendent])
SELECT distinct [Parent],[tc].[Descendent] 
FROM 
    [RelationshipTable] 
INNER JOIN [ClosureTable] as tc
    ON [Child]COLLATE DATABASE_DEFAULT = 
                      [tc].[Ancestor]COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN [ClosureTable] As tc2
    ON [Parent]COLLATE DATABASE_DEFAULT =
                      [tc2].[Ancestor] COLLATE DATABASE_DEFAULT 
    AND [tc].[Descendent]COLLATE DATABASE_DEFAULT =
                      [tc2].[Descendent]COLLATE DATABASE_DEFAULT

我的第一个问题是找到@@ ROWCOUNT的替代物...但是递归查询在mySQL中可能完全不同?我还查看了 Bill Karwin的演示文稿

My first problem is finding a substiture for @@ROWCOUNT... but perhaps recursive queries are completely different in mySQL? I've also checked out Bill Karwin's presentation

PS.由于性能问题，我需要"COLLATE DATABASE_DEFAULT".

PS. The "COLLATE DATABASE_DEFAULT" was something I needed due to performance issues..

谢谢.

推荐答案

我知道这很旧，但是我觉得您仍然需要其他人的答案，这是我从标准邻接表生成闭合表的方式:

I know this is old, but I feel you still need an answer on this for others looking, here is how I generated my closure table from my standard adjacency table:

mysql_query('TRUNCATE fec_categories_relations');

function rebuild_tree($parent)
{
    // get all children of this node
    $result = mysql_query('SELECT c.categories_id, c.parent_id, cd.categories_name FROM fec_categories c
                            INNER JOIN fec_categories_description cd ON c.categories_id = cd.categories_id
                            WHERE c.parent_id = "'.$parent.'"
                            AND      cd.language_id = 1
                            ORDER BY cd.categories_name');

    // loop through 
    while ($row = mysql_fetch_array($result))
    {       
        $update_sql = " INSERT fec_categories_relations (ancestor, descendant, length)
                        SELECT ancestor, {$row['categories_id']}, length+1
                        FROM fec_categories_relations
                        WHERE descendant = {$row['parent_id']}
                        UNION ALL SELECT {$row['categories_id']},{$row['categories_id']}, 0";

        mysql_query($update_sql);

        echo '<li>' . $update_sql . "</li>";

        rebuild_tree($row['categories_id']);
    }
}

rebuild_tree(0);

这篇关于mySQL传递闭包表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！