MYSQL实现Oracle的Start with…Connect By递归树查询

最近接触一个老项目，最初适配的数据库是oracle 后来迁移到mysql 。这个迁移是由一个已经离职的新手做的。这个新手对于很多oracle特有的函数及存储过程都没有进行迁移。导致系统很多功能都BUG了。失效了。

既然我遇到了，就尽量进行解决。刚刚遇到了一个存储过程中有使用oracle的Start with…Connect By递归树查询的。这个函数在mysql里面是没有的。但是我们可以通过自定义函数的方式来解决这个问题。

如我们有个表，表名为main_line ddl如下

 

1 2 3 4 5

CREATE TABLE `main_line` (   `line_id` varchar(17) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `pline_id` varchar(17) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,  PRIMARY KEY (`line_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

其中的pline_id为父line的line_id。

原sql中使用了(其中的v_id是一个字符串类型的参数)

select a.line_id from main_line a connect by  a.pline_id =prior a.line_id start with line_id=v_id

这个函数在mysql中没有。下面我们来实现一个自定义的函数来替代这个oracle特有的函数。

 

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20

FUNCTION nw.getchildlinelist (rootId VARCHAR(100)) RETURNS VARCHAR(1000) BEGIN     DECLARE pTemp VARCHAR(1000);         DECLARE cTemp VARCHAR(1000);                SET pTemp = '';         SET cTemp = rootId;                WHILE cTemp is not null DO         if (pTemp = '') then          SET pTemp = cTemp;          elseif(pTemp <> '') then          SET pTemp = concat(pTemp,',',cTemp);          end if;          SELECT group_concat(line_id) INTO cTemp FROM main_line            WHERE FIND_IN_SET(pline_id,cTemp)>0;        END WHILE;         RETURN pTemp;  END

这样 我们可以通过 select getchildlinelist(v_id) as line_id 来替代 select a.line_id from main_line a connect by  a.pline_id =prior a.line_id start with line_id=v_id

这样就相当于在mysql中实现了oracle的Start with…Connect By递归树查询。

 

转载：http://www.stalvan.com/568.html