mysql会员关系闭包表_如何在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

);

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?

解决方案

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();