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

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里递归查询肯定还有更好的办法的，只是我没找到，请大家多多指教。