递归
本文通过创建函数实现递归
表数据:
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 变量名 打印出变量值 。