Mysql recursive query tree table and mysql recursive tree table

Mysql recursive query tree table and mysql recursive tree table

Drop table if exists 't_ areainfo '; create table 't_ areainfo' ('id' int (11) not null AUTO_INCREMENT, 'level' int (11) DEFAULT 0, 'name' varchar (255), 'parentid' int (11), 'status' int (11) DEFAULT 0, primary key ('id ')) ENGINE = InnoDB AUTO_INCREMENT = 65 default charset = utf8; insert into't _ areainfo 'values ('1', '0', 'China', '0 ', '0'); insert into't _ areainfo 'values ('2', '0', 'North China ', '1', '0 '); insert into't _ areainfo 'values ('3', '0', 'South China region', '1', '0 '); insert into't _ areainfo 'values ('4', '0', 'beijing', '2', '0 '); insert into't _ areainfo 'values ('5', '0', 'haidian district ', '4', '0 '); insert into't _ areainfo 'values ('6', '0', 'fengtai district ', '4', '0 '); insert into't _ areainfo 'values ('7', '0', 'chaoyang district ', '4', '0 '); insert into't _ areainfo 'values ('8', '0', 'beijingxx zone 1', '4', '0 '); insert into't _ areainfo 'values ('9', '0', 'Beijing XX zone 2', '4', '0 '); insert into't _ areainfo 'values ('10', '0', 'Beijing XX district 3', '4', '0 '); insert into't _ areainfo 'values ('11', '0', 'Beijing XX district 4', '4', '0 '); insert into't _ areainfo 'values ('12', '0', 'Beijing XX district 5', '4', '0 '); insert into't _ areainfo 'values ('13', '0', 'Beijing XX zone 6', '4', '0 '); insert into't _ areainfo 'values ('14', '0', 'Beijing XX district 7', '4', '0 '); insert into't _ areainfo 'values ('15', '0', 'Beijing XX district 8', '4', '0 '); insert into't _ areainfo 'values ('16', '0', 'Beijing XX district 9', '4', '0 '); insert into't _ areainfo 'values ('17', '0', 'Beijing XX district 10', '4', '0 '); insert into't _ areainfo 'values ('18', '0', 'Beijing XX 11', '4', '0 '); insert into't _ areainfo 'values ('19', '0', 'Beijing XX zone 12', '4', '0 '); insert into't _ areainfo 'values ('20', '0', 'Beijing XX district 13', '4', '0 '); insert into't _ areainfo 'values ('21', '0', 'Beijing XX district 14', '4', '0 '); insert into't _ areainfo 'values ('22', '0', 'Beijing XX district 15', '4', '0 '); insert into't _ areainfo 'values ('23', '0', 'Beijing XX district 16', '4', '0 '); insert into't _ areainfo 'values ('24', '0', 'Beijing XX zone 17', '4', '0 '); insert into't _ areainfo 'values ('25', '0', 'Beijing XX district 18', '4', '0 '); insert into't _ areainfo 'values ('26', '0', '19', '4', '0 '); insert into't _ areainfo 'values ('27', '0', 'beijingxx zone 1', '4', '0 '); insert into't _ areainfo 'values ('28', '0', 'Beijing XX zone 2', '4', '0 '); insert into't _ areainfo 'values ('29', '0', 'Beijing XX district 3', '4', '0 '); insert into't _ areainfo 'values ('30', '0', 'Beijing XX district 4', '4', '0 '); insert into't _ areainfo 'values ('31', '0', 'Beijing XX district 5', '4', '0 '); insert into't _ areainfo 'values ('32', '0', 'Beijing XX zone 6', '4', '0 '); insert into't _ areainfo 'values ('33', '0', 'Beijing XX district 7', '4', '0 '); insert into't _ areainfo 'values ('34', '0', 'Beijing XX district 8', '4', '0 '); insert into't _ areainfo 'values ('35', '0', 'Beijing XX district 9', '4', '0 '); insert into't _ areainfo 'values ('36 ', '0', 'Beijing XX district 10', '4', '0 '); insert into't _ areainfo 'values ('37', '0', 'Beijing XX 11', '4', '0 '); insert into't _ areainfo 'values ('38', '0', 'Beijing XX zone 12', '4', '0 '); insert into't _ areainfo 'values ('39 ', '0', 'Beijing XX district 13', '4', '0 '); insert into't _ areainfo 'values ('40', '0', 'Beijing XX district 14', '4', '0 '); insert into't _ areainfo 'values ('41 ', '0', 'Beijing XX district 15', '4', '0 '); insert into't _ areainfo 'values ('42', '0', 'Beijing XX district 16', '4', '0 '); insert into't _ areainfo 'values ('43 ', '0', 'Beijing XX zone 17', '4', '0 '); insert into't _ areainfo 'values ('44', '0', 'Beijing XX district 18', '4', '0 '); insert into't _ areainfo 'values ('45', '0', '19', '4', '0 '); insert into't _ areainfo 'values ('46', '0', 'xx province 1', '1', '0 '); insert into't _ areainfo 'values ('47', '0', 'xx province 2', '1', '0 '); insert into't _ areainfo 'values ('48', '0', 'xx province 3', '1', '0 '); insert into't _ areainfo 'values ('49', '0', 'xx province 4', '1', '0 '); insert into't _ areainfo 'values ('50', '0', 'xx province 5', '1', '0 '); insert into't _ areainfo 'values ('51', '0', 'xx province 6', '1', '0 '); insert into't _ areainfo 'values ('52 ', '0', 'xx province 7', '1', '0 '); insert into't _ areainfo 'values ('53', '0', 'xx province 8', '1', '0 '); insert into't _ areainfo 'values ('54', '0', 'xx province 9', '1', '0 '); insert into't _ areainfo 'values ('55', '0', 'xx province 10', '1', '0 '); insert into't _ areainfo 'values ('56', '0', 'xx province 11', '1', '0 '); insert into't _ areainfo 'values ('57 ', '0', 'xx province 12', '1', '0 '); insert into't _ areainfo 'values ('58 ', '0', 'xx province 13', '1', '0 '); insert into't _ areainfo 'values ('59', '0', 'xx province 14', '1', '0 '); insert into't _ areainfo 'values ('60', '0', 'xx province 15', '1', '0 '); insert into't _ areainfo 'values ('61 ', '0', 'xx province 16', '1', '0 '); insert into't _ areainfo 'values ('62 ', '0', 'xx province 17', '1', '0 '); insert into't _ areainfo 'values ('63 ', '0', 'xx province 18', '1', '0 '); insert into't _ areainfo 'values ('64', '0', 'xx province 19', '1', '0'); -- Method 1: use function to obtain the IDs of all subnodes -- query the input areaId and all its subnodes delimiter // drop function if exists queryChildrenAreaInfo; create function 'querychildrenareainfo' (areaId INT) returns varchar (4000) BEGINDECLARE sTemp VARCHAR (4000); DECLARE sTempChd VARCHAR (4000); SET sTemp = '$'; SET sTempChd = cast (areaId as char ); WHILE sTempChd is not null doset 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 // 2 -- call method select queryChildrenAreaInfo (1); select * from t_areainfo where FIND_IN_SET (id, queryChildrenAreaInfo (20 )); /* delimiter // create function 'delimiter' (rootId INT) RETURNS varchar (1000) BEGINDECLARE sTemp VARCHAR (1000); DECLARE sTempChd VARCHAR (1000); SET sTemp = '$ '; SET sTempChd = cast (rootId as CHAR); WHILE sTempChd is not null DOSET 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 ;*/