mysql递归查询所有数据的方法：
1、创建表
drop table if exists `t_areainfo`; create table `t_areainfo` ( `id` int(11) not '0' auto_increment, `level` int(11) default '0', `name` varchar(255) default '0', `parentid` int(11) default '0', `status` int(11) default '0', primary key (`id`) ) engine=innodb auto_increment=65 default charset=utf8;
2、初始数据
insert into `t_areainfo` values ('1', '0', '中国', '0', '0');
insert into `t_areainfo` values ('2', '0', '华北区', '1', '0');
insert into `t_areainfo` values ('3', '0', '华南区', '1', '0');
insert into `t_areainfo` values ('4', '0', '北京', '2', '0');
insert into `t_areainfo` values ('5', '0', '海淀区', '4', '0');
insert into `t_areainfo` values ('6', '0', '丰台区', '4', '0');
insert into `t_areainfo` values ('7', '0', '朝阳区', '4', '0');
insert into `t_areainfo` values ('8', '0', '北京xx区1', '4', '0');
insert into `t_areainfo` values ('9', '0', '北京xx区2', '4', '0');
insert into `t_areainfo` values ('10', '0', '北京xx区3', '4', '0');
insert into `t_areainfo` values ('11', '0', '北京xx区4', '4', '0');
insert into `t_areainfo` values ('12', '0', '北京xx区5', '4', '0');
insert into `t_areainfo` values ('13', '0', '北京xx区6', '4', '0');
insert into `t_areainfo` values ('14', '0', '北京xx区7', '4', '0');
insert into `t_areainfo` values ('15', '0', '北京xx区8', '4', '0');
insert into `t_areainfo` values ('16', '0', '北京xx区9', '4', '0');
insert into `t_areainfo` values ('17', '0', '北京xx区10', '4', '0');
insert into `t_areainfo` values ('18', '0', '北京xx区11', '4', '0');
insert into `t_areainfo` values ('19', '0', '北京xx区12', '4', '0');
insert into `t_areainfo` values ('20', '0', '北京xx区13', '4', '0');
insert into `t_areainfo` values ('21', '0', '北京xx区14', '4', '0');
insert into `t_areainfo` values ('22', '0', '北京xx区15', '4', '0');
insert into `t_areainfo` values ('23', '0', '北京xx区16', '4', '0');
insert into `t_areainfo` values ('24', '0', '北京xx区17', '4', '0');
insert into `t_areainfo` values ('25', '0', '北京xx区18', '4', '0');
insert into `t_areainfo` values ('26', '0', '北京xx区19', '4', '0');
insert into `t_areainfo` values ('27', '0', '北京xx区1', '4', '0');
insert into `t_areainfo` values ('28', '0', '北京xx区2', '4', '0');
insert into `t_areainfo` values ('29', '0', '北京xx区3', '4', '0');
insert into `t_areainfo` values ('30', '0', '北京xx区4', '4', '0');
insert into `t_areainfo` values ('31', '0', '北京xx区5', '4', '0');
insert into `t_areainfo` values ('32', '0', '北京xx区6', '4', '0');
insert into `t_areainfo` values ('33', '0', '北京xx区7', '4', '0');
insert into `t_areainfo` values ('34', '0', '北京xx区8', '4', '0');
insert into `t_areainfo` values ('35', '0', '北京xx区9', '4', '0');
insert into `t_areainfo` values ('36', '0', '北京xx区10', '4', '0');
insert into `t_areainfo` values ('37', '0', '北京xx区11', '4', '0');
insert into `t_areainfo` values ('38', '0', '北京xx区12', '4', '0');
insert into `t_areainfo` values ('39', '0', '北京xx区13', '4', '0');
insert into `t_areainfo` values ('40', '0', '北京xx区14', '4', '0');
insert into `t_areainfo` values ('41', '0', '北京xx区15', '4', '0');
insert into `t_areainfo` values ('42', '0', '北京xx区16', '4', '0');
insert into `t_areainfo` values ('43', '0', '北京xx区17', '4', '0');
insert into `t_areainfo` values ('44', '0', '北京xx区18', '4', '0');
insert into `t_areainfo` values ('45', '0', '北京xx区19', '4', '0');
insert into `t_areainfo` values ('46', '0', 'xx省1', '1', '0');
insert into `t_areainfo` values ('47', '0', 'xx省2', '1', '0');
insert into `t_areainfo` values ('48', '0', 'xx省3', '1', '0');
insert into `t_areainfo` values ('49', '0', 'xx省4', '1', '0');
insert into `t_areainfo` values ('50', '0', 'xx省5', '1', '0');
insert into `t_areainfo` values ('51', '0', 'xx省6', '1', '0');
insert into `t_areainfo` values ('52', '0', 'xx省7', '1', '0');
insert into `t_areainfo` values ('53', '0', 'xx省8', '1', '0');
insert into `t_areainfo` values ('54', '0', 'xx省9', '1', '0');
insert into `t_areainfo` values ('55', '0', 'xx省10', '1', '0');
insert into `t_areainfo` values ('56', '0', 'xx省11', '1', '0');
insert into `t_areainfo` values ('57', '0', 'xx省12', '1', '0');
insert into `t_areainfo` values ('58', '0', 'xx省13', '1', '0');
insert into `t_areainfo` values ('59', '0', 'xx省14', '1', '0');
insert into `t_areainfo` values ('60', '0', 'xx省15', '1', '0');
insert into `t_areainfo` values ('61', '0', 'xx省16', '1', '0');
insert into `t_areainfo` values ('62', '0', 'xx省17', '1', '0');
insert into `t_areainfo` values ('63', '0', 'xx省18', '1', '0');
insert into `t_areainfo` values ('64', '0', 'xx省19', '1', '0');
3、向下递归
利用find_in_set()函数和group_concat()函数实现递归查询：
drop function if exists querychildrenareainfo; delimiter ;; create function querychildrenareainfo(areaid int) returns varchar(4000) begin declare stemp varchar(4000); declare stempchd varchar(4000); set stemp='$'; set stempchd = cast(areaid as char); while stempchd is not null do set stemp= concat(stemp,',',stempchd); select group_concat(id) into stempchd from t_areainfo where find_in_set(parentid,stempchd)>0; end while; return stemp; end ;; delimiter ;
4、调用方式
select querychildrenareainfo(1);
查询id为"4"下面的所有节点
select * from t_areainfo where find_in_set(id,querychildrenareainfo(4));
5、向上递归
drop function if exists querychildrenareainfo1; delimiter;; create function querychildrenareainfo1(areaid int) returns varchar(4000) begin declare stemp varchar(4000); declare stempchd varchar(4000); set stemp='$'; set stempchd = cast(areaid as char); set stemp = concat(stemp,',',stempchd); select parentid into stempchd from t_areainfo where id = stempchd; while stempchd <> 0 do set stemp = concat(stemp,',',stempchd); select parentid into stempchd from t_areainfo where id = stempchd; end while; return stemp; end ;; delimiter ;
6、调用方式
查询id为"7"的节点的所有上级节点：
select * from t_areainfo where find_in_set(id,querychildrenareainfo1(7));
