如何在MySQL中将关系层次结构转换为闭包表? [英] How do I convert a relational hierarchy to a closure table in MySQL?

问题描述

我最近开始使用闭包表，并且该表正在运行当我不得不将简单的父子关系从人转换为血统时，这很好，但是现在我必须将祖父母-父母-孩子和曾祖父母-父母-孩子的关系，甚至更长的关系转换为闭包表.这是我需要从中获取关系的表:

I recently started using a closure table, and it was working fine when I had to convert simple parent-child relationships from person to lineage, but now I have to convert grandparent-parent-child and great-grandparent-parent-child relationships, and maybe even longer relationships than that into the closure table. This is the table I need to pull the relationships from:

CREATE TABLE `person` (
    `person` BIGINT UNSIGNED NOT NULL ,
    `parent` BIGINT UNSIGNED NULL ,
    PRIMARY KEY (`person`) ,
    INDEX `idx_person_has_parent` (`parent` ASC),
    CONSTRAINT `fk_person_has_parent`
        FOREIGN KEY (`parent`)
        REFERENCES `person` (`person`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

这是我需要转换为的表:

This is the table I need to convert to:

CREATE TABLE `lineage` (
    `ancestor` BIGINT UNSIGNED NOT NULL,
    `descendant` BIGINT UNSIGNED NOT NULL,
    `length` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`ancestor`, descendant`),
    CONSTRAINT `fk_ancestor_has_descendant`
        FOREIGN KEY (`descendant`)
        REFERENCES `person` (`person`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT `fk_descendant_has_ancestor`
        FOREIGN KEY (`ancestor`)
        REFERENCES `person` (`person`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

我遇到的最大问题是为祖父母的子女保存正确的长度，该长度应为2.祖父母和父母子女均为1，当与自己(孩子，父母，父母等).我可以在查询中执行此操作，还是需要某种程序?

The biggest problem I'm having is saving the correct length for entries of grandparent-child, which should be length 2. Grandparent-parent and parent-child are both 1, and each of them has a length 0 when associated with themselves (child-child, parent-parent, etc.). Can I do this in a query or will it require some sort of program?

推荐答案

这是一个古老的脚本，我在6到7年前完成了此工作，以在PHP/Propel中完成此任务.希望它对其他人有用:

This is an ancient script that I completed 6-7 years ago to accomplish this task in PHP/Propel. Hopefully it's useful to someone else:

require_once 'common/Autoloader.php';
require_once 'propel/Propel.php';
\Propel::init('db/runtime-conf.php');

function truncateHierarchy(\PropelPDO $propel) {
    /* @var $state \PropelStatement */
    $state = $propel->prepare('TRUNCATE database.person_hierarchy');
    $state->execute();
}

function insertHierarchy(\PropelPDO $propel, $length) {
    if ($length == 0) {
        $state = $propel->prepare('INSERT INTO database.person_hierarchy SELECT id, id, 0 FROM person;');
        $state->execute();
        return $state->rowCount();
    } else if ($length == 1) {
        $state = $propel->prepare('INSERT INTO database.person_hierarchy SELECT parent_person_id, id, 1 FROM person WHERE id != parent_person_id;');
        $state->execute();
        return $state->rowCount();
    } else {
        $sql = "INSERT INTO database.person_hierarchy \n";
        $sql .= "SELECT p.parent_person_id, c" . ($length - 1) . ".id, " . $length . " FROM database.person AS p \n";
        for ($i = 1; $i <= $length - 1; $i++) {
            $sql .= "LEFT JOIN person AS c" . $i . " ON " . ($i == 1 ? 'p.id' : 'c' . ($i - 1) . '.id') . " = c" . $i . ".parent_person_id \n";
        }
        $sql .= "WHERE p.parent_person_id != p.id \n";
        for ($i = 1; $i <= $length - 1; $i++) {
            $sql .= "AND c" . $i . ".parent_person_id != c" . $i . ".id \n";
        }
        echo $sql;
        $state = $propel->prepare($sql);
        $state->execute();
        return $state->rowCount();
    }
}

/* @var $connect \PropelConnection */
$propel = \Propel::getConnection();
$propel->beginTransaction();

try {
    truncateHierarchy($propel);
    $propel->commit();
} catch (\Exception $e) {
    error_log_exc($e);
    echo "Failed to truncate person hierarchy!\n";
    $propel->rollBack();
    exit();
}

$length = 0;
$inserts = -1;
while ($inserts !== 0 || $length != 10) {
    $propel->beginTransaction();
    try {
        $inserts = insertHierarchy($propel, $length);
        if ($inserts == 0) { 
            echo "No persons exist at length " . $length . ".\n";
        } else {
            echo $inserts . " rows inserted for length " . $length . ".\n";
        }
        $length++;
        $propel->commit();
    } catch (\Exception $e) {
        error_log_exc($e);
        echo "Failed to regenerate person hierarchy!\n";
        $propel->rollBack();
        exit();
    }
}

echo "Regenerated person hierarchy!\n";
exit();

这篇关于如何在MySQL中将关系层次结构转换为闭包表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！