mysql自定义函数实现单表递归操作

1.初始化表

CREATE TABLE `test_classify` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`parent_id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=14
;

2.导入数据

INSERT INTO `test_classify` (`id`, `parent_id`, `name`) VALUES
	(1, 0, '酷学院'),
	(2, 1, '创新门店'),
	(3, 1, '产研中心'),
	(4, 1, '客户成功中心'),
	(5, 2, '市场部'),
	(6, 2, '产品营销部'),
	(7, 2, '内容产品部'),
	(8, 3, 'SAAS产品部'),
	(9, 4, '交付部'),
	(10, 4, '成功服务部'),
	(11, 9, '产品组'),
	(12, 9, '实施组'),
	(13, 9, '开发组');

3.创建递归函数

delimiter //
CREATE FUNCTION  parent_name(in_id INT)
RETURNS  VARCHAR(500)
BEGIN 
  DECLARE result VARCHAR(500);
  DECLARE vir_name VARCHAR(50) ;
  DECLARE result_parent_id INT;
  SET result_parent_id =in_id;
  repeat 
     SELECT NAME INTO vir_name FROM test_classify WHERE id = result_parent_id LIMIT 1;
     SET result = CONCAT_WS('/',vir_name,result);
     SELECT parent_id INTO result_parent_id FROM test_classify WHERE id = result_parent_id LIMIT 1;
  until result_parent_id =0 END repeat;
   RETURN result;
END //
delimiter ;

4.进行递归查询

SELECT test_classify.*,parent_name(id) AS path FROM test_classify;