记Mysql中递归查询的egg ache经历

xywuyiba7

Mysql中针对树形结构，查找一个节点的所有孩子（包括孙子）节点，及查找一个节点的所有祖先节点。
表结构：
CREATE TABLE `myboard_group` (
`mygroup_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`mygroup_name` varchar(64) NOT NULL,
`parent_id` int(11) unsigned NOT NULL DEFAULT '0',
`create_time` datetime NOT NULL,
`comment` varchar(256) DEFAULT NULL,
`has_child` int(11) unsigned DEFAULT '0',
PRIMARY KEY (`mygroup_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



由于mysql里没有oracle中的start with …connnect by…这种查询树形结构的便捷方法，所以只能自己想办法了。
思路参照网上的方法，写一个存储过程，采用一个表来存储中间数据。
DROP PROCEDURE
IF EXISTS getGroupChildren;
CREATE PROCEDURE getGroupChildren (IN parent_idd INT)
BEGIN
DECLARE
lev INT;
DECLARE
child_count INT;
SET lev = 1;
SET child_count = 0;
DROP TABLE
IF EXISTS group_tmp1;
CREATE TABLE group_tmp1 (
mygroup_id INT (11),
mygroup_name VARCHAR (64),
parent_id INT (11),
`create_time` datetime,
`comment` VARCHAR (256),
levv INT
) engine=memory;
INSERT INTO group_tmp1 SELECT
mygroup_id,
mygroup_name,
parent_id,
`create_time`,
`comment`,
1
FROM
` myboard_group`
WHERE
parent_id = parent_idd;
SELECT
count(1) INTO child_count
FROM
` myboard_group`
WHERE
parent_id = parent_idd;
WHILE child_count > 0 DO
SET lev = lev + 1;
INSERT INTO group_tmp1 SELECT
t.mygroup_id,
t.mygroup_name,
t.parent_id,
t.`create_time`,
t.`comment`,
lev
FROM
` myboard_group` t
JOIN group_tmp1 a ON t.parent_id = a.mygroup_id
AND a.levv = lev - 1;
SELECT
count(1) INTO child_count
FROM
` myboard_group` t
JOIN group_tmp1 a ON t.parent_id = a.mygroup_id
AND a.levv = lev - 1;
END
WHILE;
SELECT
*
FROM
group_tmp1;
DROP TABLE group_tmp1;
END;

调用该存储过程
CALL getGroupChildren (1);
结果

问题来了…
这个方案的思路比较明确，在存储过程中采用一个“临时表”，一层一层的将孩子节点的数据取出来依次放入该表，最后达到取出整棵树的目的，但是，问题出现了，就出在这个“临时表”上。Mysql中在一次查询中只能访问临时表（TEMPORARY TABLE）一次，而这里调用一次存储过程需要访问多次，临时表显然已不能满足需求。如果改为采用普通的永久表，在并发访问的情况下又有可能产生数据冲突（虽然可以采用向该表里加一个字段写入该次访问id，查询时按访问id查询的方式避免这个问题），所以最后只能采用上面看到的内存表（创建表时加engine=memory），勉强可以满足要求（Mysql中临时表可以单独授权访问权限，而把内存表的访问权限跟普通表划在了一起，所以只能将普通表的访问权限放开给该用户，带来了安全隐患）。
查找一个节点的所有祖先节点的实现如下：
DROP PROCEDURE
IF EXISTS getGroupAncestor;
CREATE PROCEDURE getGroupAncestor (IN child_idd INT)
BEGIN
DECLARE
lev INT;
DECLARE
father_id
INT;
SET lev = 1;
SET father_id = 1;
DROP TABLE
IF EXISTS group_tmp2;
CREATE TABLE group_tmp2 (
mygroup_id INT (11),
mygroup_name VARCHAR (64),
parent_id INT (11),
`create_time` datetime,
`comment` VARCHAR (256),
levv INT
) ENGINE=memory;
INSERT INTO group_tmp2 SELECT
t.mygroup_id,
t.mygroup_name,
t.parent_id,
t.`create_time`,
t.`comment`,
1
FROM
` myboard_group` t
WHERE EXISTS (select 1 from ` myboard_group` m where m.mygroup_id=child_idd and m.parent_id=t.mygroup_id);
SELECT
t.mygroup_id INTO father_id
FROM
` myboard_group` t
WHERE EXISTS (select 1 from ` myboard_group` m where m.mygroup_id=child_idd and m.parent_id=t.mygroup_id);
WHILE father_id > 0 DO
SET lev = lev + 1;
INSERT INTO group_tmp2 SELECT
t.mygroup_id,
t.mygroup_name,
t.parent_id,
t.`create_time`,
t.`comment`,
lev
FROM
` myboard_group` t
JOIN group_tmp2 a ON a.parent_id=t.mygroup_id
AND a.levv = lev - 1;
SELECT
t.parent_id INTO father_id
FROM
` myboard_group` t
JOIN group_tmp2 a ON a.parent_id=t.mygroup_id
AND a.levv = lev - 1;
END WHILE;
SELECT
*
FROM
group_tmp2;
DROP TABLE if EXISTS  group_tmp2;
END;

调用该存储过程
CALL getGroupAncestor (6);

结果

我觉得，在mysql里递归查询肯定还有更好的办法的，只是我没找到，请大家多多指教。