非专业处理：Oracle->MySQL的Connect by迁移

这两天在弄一个系统迁移，以适应小平台的要求。数据库要从Oracle移植到Mysql。
比较了种种，包括数据类型、主键自增/序列、字符串函数之后，就剩下一个“[b]层次递推查询[/b]”最难办。

具体是这样的：
Oracle的connect by语句能够很好的支持：
1、只根据id和parentId两个字段，便可以查找一个结点的[b]所有子孙结点[/b]
2、只根据Id和parentId连个字段，便可以查找一个结点的根(表是一个森林，非树)

MYSQL对等的语句处理这样的功能。

示例的表结构(MySQL)：
[code]
CREATE TABLE `node` (
`id` bigint(20) NOT NULL auto_increment,
`parentId` varchar(20) default NULL,
`name` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
[/code]

第2个问题比较好解决，在此不是主要的讨论点。关键是第1个。

比如有如下数据：
[code]
mysql> select * from node;
+----+----------+-------+
| id | parentId | name |
+----+----------+-------+
| 1 | NULL | root |
| 2 | 1 | two |
| 3 | 1 | three |
| 4 | 2 | four |
| 5 | 2 | five |
| 6 | 3 | six |
| 7 | 3 | seven |
| 8 | 4 | eight |
+----+----------+-------+
8 rows in set (0.02 sec)
[/code]
那么id=2的所有子孙，将是id为：4，5，8的纪录。

本人不是数据库专家，所以确实很郁闷。不过还好对MYSQL还算有所了解，做了一下一个存储过程来应付：
(该存储过程，[b]不具有通用性[/b]，针对的是特定的表名以及id,parentId字段名称，不同的表，需要不同的存储过程，采用selectXxxxxPosterity的命名方式；而且要求子孙的id必须大于父亲的id。)
[code]
CREATE PROCEDURE `selectNodePosterity`(IN startId BIGINT)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN

DECLARE _id bigint DEFAULT 0;
DECLARE _path VARCHAR(255);
DECLARE _last bigint DEFAULT 0;

CREATE TEMPORARY TABLE IF NOT EXISTS `temp_table` (
`id` bigint(20) NOT NULL auto_increment,
`path` varchar(20) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB TYPE = HEAP;

delete from temp_table;

insert into temp_table(id, path)
select src.id, src.id from node src where id=startId;

set _id = startId;
set _path = startId;

WHILE _id <> 0 DO
insert into temp_table(id, path)
select src.id, concat(concat(_path, '/'), src.id)
from node src where src.parentId=_id;
set _last = _id;
set _id = 0;
select id, path into _id, _path from temp_table where id>_last limit 1;
END WHILE;

select src.*, temp_table.path from temp_table, node src where temp_table.id= src.id order by temp_table.path;
END;
[/code]
运行效果：
[code]
mysql> call selectNodePosterity(2);
+----+----------+-------+-------+
| id | parentId | name | path |
+----+----------+-------+-------+
| 2 | 1 | two | 2 |
| 4 | 2 | four | 2/4 |
| 8 | 4 | eight | 2/4/8 |
| 5 | 2 | five | 2/5 |
+----+----------+-------+-------+
4 rows in set (0.00 sec)

Query OK, 0 rows affected, 2 warnings (0.02 sec)
[/code]