MYSQL之递归01子查父

递归
本文通过创建函数实现递归
表数据：

INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('1', '广东省', '0', '广东省', '1');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('2', '深圳市', '1', '广东省', '2');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('3', '广州市', '1', '广东省', '2');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('4', '佛山市', '1', '广东省', '2');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('5', '南山区', '2', '深圳市', '3');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('6', '福田区', '2', '深圳市', '3');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('7', '西丽镇', '5', '南山区', '4');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('8', '茶光村', '7', '西丽镇', '5');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('9', '河南省', '0', '河南省', '1');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('10', '商丘市', '9', '河南省', '2');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('11', '睢阳区', '10', '商丘市', '3');
INSERT INTO `testsql`.`basic_area` (`id`, `name`, `parentid`, `parentname`, `lvl`) VALUES ('12', '郑州市', '9', '河南省', '2');

函数：

CREATE ` FUNCTION `Func_area02`(tname varchar(4000)) RETURNS varchar(4000) CHARSET utf8mb4
BEGIN
DECLARE Result VARCHAR(4000);
DECLARE str VARCHAR(4000);
DECLARE pname VARCHAR(4000);
DECLARE pp int;


SELECT parentid into pp FROM basic_area WHERE name=tname;

 set pname=tname; 
 set Result=tname;

WHILE pp <>0 DO

set str=pname; --存储父

-- SELECT ifnull((SELECT parentname  FROM basic_area WHERE name=str),'')into pname;
SELECT parentname into pname FROM basic_area WHERE name=str; --根据变量查找父，同时重新给父变量赋值当前最新的父，用于下次循环查找子
select parentid  FROM basic_area WHERE name=pname into pp;-- 0
SET Result= CONCAT(Result,'->',pname);
HILE;
RETURN Result;
END

效果：

select *,func_area02(name) as path  from basic_area;

总结：整体思路就是使用变量临时存储。
可能遇到的问题：
1.创建函数时提示错误：
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
解决：
set global log_bin_trust_function_creators=TRUE;
2.调用函数时提示错误：
Not allowed to return a result set from a function
function函数中不允许使用 select 变量名 打印出变量值 。