mysql 邻接表,MySQL-邻接表模型-获取深度

I have an organization table that has an id, a parent_id, and a name column. There are roughly 50k rows in this table. There is just one top level parent and the rest are all under that. In Oracle, I am able to easily retrieve the current depth of a particular organization with the level pseudocolumn:

SELECT id, parent_id, level, name

FROM organizations

START WITH parent_id = 1

CONNECT BY PRIOR id = parent_id

I am at a loss of what the proper way to do the above in MySQL is. I need to fetch the entire tree along with the node's depth in one query.

There are a plethora of questions on StackOverflow that have to do with this, but none of them seem to have a really good answer to it, mostly links to blogs with dubious solutions. Surely this is doable in some sort of straight-forward manner?

Unfortunately modifying the table in any way is not an option, so nested sets is not a possibility.

解决方案

This is totally hilarious. I just picked up a +50 bounty on a similar question literally yesterday : Using MySQL query to traverse rows to make a recursive tree

I will post the same stored procedures along with the examples from my DBA StackExchange answer:

Code to Get the Parent for Any Given Node

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetParentIDByID` $$

CREATE FUNCTION `junk`.`GetParentIDByID` (GivenID INT) RETURNS INT

DETERMINISTIC

BEGIN

DECLARE rv INT;

SELECT IFNULL(parent_id,-1) INTO rv FROM

(SELECT parent_id FROM pctable WHERE id = GivenID) A;

RETURN rv;

END $$

DELIMITER ;

Code to Get the Ancenstry for Any Given Node

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetAncestry` $$

CREATE FUNCTION `junk`.`GetAncestry` (GivenID INT) RETURNS VARCHAR(1024)

DETERMINISTIC

BEGIN

DECLARE rv VARCHAR(1024);

DECLARE cm CHAR(1);

DECLARE ch INT;

SET rv = '';

SET cm = '';

SET ch = GivenID;

WHILE ch > 0 DO

SELECT IFNULL(parent_id,-1) INTO ch FROM

(SELECT parent_id FROM pctable WHERE id = ch) A;

IF ch > 0 THEN

SET rv = CONCAT(rv,cm,ch);

SET cm = ',';

END IF;

END WHILE;

RETURN rv;

END $$

DELIMITER ;

Code to Get the Family Tree (or Descendants) for Any Given Node

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$

CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT) RETURNS varchar(1024) CHARSET latin1

DETERMINISTIC

BEGIN

DECLARE rv,q,queue,queue_children VARCHAR(1024);

DECLARE queue_length,front_id,pos INT;

SET rv = '';

SET queue = GivenID;

SET queue_length = 1;

WHILE queue_length > 0 DO

SET front_id = FORMAT(queue,0);

IF queue_length = 1 THEN

SET queue = '';

ELSE

SET pos = LOCATE(',',queue) + 1;

SET q = SUBSTR(queue,pos);

SET queue = q;

END IF;

SET queue_length = queue_length - 1;

SELECT IFNULL(qc,'') INTO queue_children

FROM (SELECT GROUP_CONCAT(id) qc

FROM pctable WHERE parent_id = front_id) A;

IF LENGTH(queue_children) = 0 THEN

IF LENGTH(queue) = 0 THEN

SET queue_length = 0;

END IF;

ELSE

IF LENGTH(rv) = 0 THEN

SET rv = queue_children;

ELSE

SET rv = CONCAT(rv,',',queue_children);

END IF;

IF LENGTH(queue) = 0 THEN

SET queue = queue_children;

ELSE

SET queue = CONCAT(queue,',',queue_children);

END IF;

SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;

END IF;

END WHILE;

RETURN rv;

END $$

To demonstrate the execution of everything, here is the sample data

USE junk

DROP TABLE IF EXISTS pctable;

CREATE TABLE pctable

(

id INT NOT NULL AUTO_INCREMENT,

parent_id INT,

PRIMARY KEY (id)

) ENGINE=MyISAM;

INSERT INTO pctable (parent_id) VALUES (0);

INSERT INTO pctable (parent_id) SELECT parent_id+1 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+2 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+3 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+4 FROM pctable;

INSERT INTO pctable (parent_id) SELECT parent_id+5 FROM pctable;

SELECT * FROM pctable;

Here is the query to see all Parents, Ancestries, and Family Trees

SELECT

id,parent_id,

GetParentIDByID(id),

GetAncestry(id),

GetFamilyTree(id)

FROM pctable;

Give it a Try !!!