MySQL实现Oracle中的SYS_CONNECT_BY_PATH功能

MySQL实现Oracle中的SYS_CONNECT_BY_PATH功能：

准备建表SQL

DROP TABLE IF EXISTS `tb_dict_category_config`;
CREATE TABLE `tb_dict_category_config` (
  `id` varchar(100) DEFAULT NULL,
  `pid` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `isparent` varchar(6) DEFAULT 'false'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_dict_category_config
-- ----------------------------
INSERT INTO `tb_dict_category_config` VALUES ('103', '12', '分类60', 'false');
INSERT INTO `tb_dict_category_config` VALUES ('1', '0', '全部分类', 'true');
INSERT INTO `tb_dict_category_config` VALUES ('12', '1', '分类6', 'true');

接下来是创建函数getParentList的SQL：

CREATE FUNCTION `getParentList` (rootId VARCHAR (50)) RETURNS VARCHAR (1000) 
BEGIN
DECLARE sParentList VARCHAR (1000) ;
DECLARE sParentTemp VARCHAR(1000); 
DECLARE curName VARCHAR(1000);
SET sParentTemp =CAST(rootId AS CHAR); 
WHILE sParentTemp IS NOT NULL DO 
select name into curName from tb_dict_category_config where id = sParentTemp;
IF (sParentList IS NOT NULL) THEN 
SET sParentList = CONCAT(curName,'->',sParentList); 
ELSE 
SET sParentList = CONCAT(curName); 
END IF; 
SELECT GROUP_CONCAT(pid) INTO sParentTemp FROM tb_dict_category_config WHERE FIND_IN_SET(id,sParentTemp)>0; 
END WHILE; 
RETURN substring(sParentList,13); 
END

执行结果：

select getParentList(103);

如下图：

参考：

1. http://blog.csdn.net/stevendbaguo/article/details/22791777；