Shulou(Shulou.com)06/01 Report--

Editor to share with you the method of mysql to achieve recursive query, I hope you will learn a lot after reading this article, let's discuss it together!

The method of mysql to realize recursive query: first create the table and initialize the data, then recursively down, using the [find_in_set ()] function and [group_concat ()] function to realize the recursive query.

The method of realizing recursive query by mysql:

1. Create a table

DROP TABLE IF EXISTS `tareainfo`; CREATE TABLE `tareainfo` (`id` int (11) NOT'0' AUTO_INCREMENT, `level` int (11) DEFAULT '0levels, `name` varchar (255) DEFAULT' 0cycles, `parentId` int (11) DEFAULT '0cycles, `status` int (11) DEFAULT' 0cycles, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

2. Initial data

INSERT INTO `tareainfo` VALUES ('1th, 0th, China', 0th,'0'); INSERT INTO `tareainfo` VALUES ('2mm,' 0th, 'North China', '1th,' 0'); INSERT INTO `tareainfo` VALUES ('34th,' 0th, 'South China', '1th,' 0') INSERT INTO `tareainfo` VALUES ('4months,' 0cycles, 'Beijing', '2cycles,' 0'); INSERT INTO `tareainfo` VALUES ('5miles,' 0cycles, 'Haidian District', '4zones,' 0'); INSERT INTO `tareainfo` VALUES ('6months,' 0cycles, 'Fengtai District', '4bands,' 0') INSERT INTO `tareainfo` VALUES ('7miles,' 0cycles, 'Chaoyang District', '4cycles,' 0'); INSERT INTO `tareainfo` VALUES ('8levels,' 0cycles, 'Beijing XX District 1Qing,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('9levels,' 0cycles, 'Beijing XX District 2cities,' 4cycles,'0') INSERT INTO `tareainfo` VALUES ('10cm,' 0cycles, 'Beijing XX District 315,' 4months,'0'); INSERT INTO `tareainfo` VALUES ('11,0,414,' 0'); INSERT INTO `tareainfo` VALUES ('123,' 03, 'Beijing XX District 515,' 44th,'0') INSERT INTO `tareainfo` VALUES ('13th,' 04th,'0'); INSERT INTO `tareainfo` VALUES ('14th,' 0th, 'Beijing XX District 7', '44th,' 0'); INSERT INTO `tareainfo` VALUES ('15levels,' 0cycles, 'Beijing XX District 8miles,' 4cycles,'0') INSERT INTO `tareainfo` VALUES ('16,'0, 'Beijing XX District 9,' 4,'0'); INSERT INTO `tareainfo` VALUES ('17,'0, Beijing XX District 10,'4,'0'); INSERT INTO `tareainfo` VALUES ('18,'0, Beijing XX District 11,'4,'0') INSERT INTO `tareainfo` VALUES ('1900,' 04th,'0'); INSERT INTO `tareainfo` VALUES ('20,0,13,4000,' 0'); INSERT INTO `tareainfo` VALUES ('21th,' 0th, 'Beijing XX District 14,' 44th,'0') INSERT INTO `t _ areainfo` VALUES ('22,'0, 'Beijing XX District 15,' 4,'0'); INSERT INTO `tareainfo` VALUES ('23,'0, Beijing XX District 16,'4,'0'); INSERT INTO `tareainfo` VALUES ('24,'0, Beijing XX District 17,'4,'0') INSERT INTO `tareainfo` VALUES ('25levels,' 0cycles, 'Beijing XX District 1815,' 4months,'0'); INSERT INTO `tareainfo` VALUES ('26levels,' 0cycles, 'Beijing XX District 190s,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('27levels,' 0cycles, 'Beijing XX District 1Qing,' 4cycles,'0') INSERT INTO `tareainfo` VALUES ('28levels,' 0cycles, 'Beijing XX District 2months,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('29levels,' 0cycles, 'Beijing XX District 3bands,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('30cycles,' 0cycles, 'Beijing XX District 4bands,' 4cycles,'0') INSERT INTO `tareainfo` VALUES ('31miles,' 0cycles, 'Beijing XX District 5 minutes,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('32levels,' 0cycles, 'Beijing XX District 6bands,' 4cycles,'0'); INSERT INTO `tareainfo` VALUES ('33rd,' 0cycles, 'Beijing XX District 7bands,' 4cycles,'0') INSERT INTO `tareainfo` VALUES ('344th,' 04th, 'Beijing XX District 84th,' 44th,'0'); INSERT INTO `tareainfo` VALUES ('354th,' 04th,'0'); INSERT INTO `tareainfo` VALUES ('36th,' 0th, 'Beijing XX District 104th,' 44th,'0') INSERT INTO `tareainfo` VALUES ('374th,' 04th,'0'); INSERT INTO `tareainfo` VALUES ('38th,' 04th,'0'); INSERT INTO `tareainfo` VALUES ('39th,' 0th, 'Beijing XX District 13th,' 44th,'0') INSERT INTO `tareainfo` VALUES ('40,'0, 'Beijing XX District 14,' 4,'0'); INSERT INTO `tareainfo` VALUES ('41,'0, Beijing XX District 15,'4,'0'); INSERT INTO `tareainfo` VALUES ('42,'0, Beijing XX District 16,'4,'0') INSERT INTO `t _ areainfo` VALUES ('43,'0, 'Beijing XX District 17,' 4,'0'); INSERT INTO `tareainfo` VALUES ('44,'0, Beijing XX District 18,'4,'0'); INSERT INTO `tareainfo` VALUES ('45,'0, Beijing XX District 19,'4,'0') INSERT INTO `tareainfo` VALUES ('46 years,'0 provinces,'xx provinces 1 province,'1 provinces,'0'); INSERT INTO `t _ areainfo` VALUES ('47 provinces,'0 provinces,'xx provinces 2 provinces,'1 provinces,'0'); INSERT INTO `tareainfo` VALUES ('48 provinces,'0 provinces,'xx provinces 3 provinces,'1 provinces,'0') INSERT INTO `tareainfo` VALUES ('49 years,'0 provinces,'xx provinces 4 provinces,'1 provinces,'0'); INSERT INTO `tareainfo` VALUES ('50 provinces,'0 provinces,'xx provinces 5 provinces,'1 provinces,'0'); INSERT INTO `t _ areainfo` VALUES ('51 provinces,'0 provinces,'xx provinces 6 provinces,'1 provinces,'0') INSERT INTO `tareainfo` VALUES ('52percent,' 0province,'xx province 7 percent, '1percent,' 0'); INSERT INTO `tareainfo` VALUES ('53 province, '0province,' xx province 8percent, '1month,' 0'); INSERT INTO `tareainfo` VALUES ('54 provinces, '0provinces,' xx provinces 9 provinces,'1 provinces,'0') INSERT INTO `tareainfo` VALUES ('55 years,'0 provinces,'xx provinces 10 years,'1 provinces,'0'); INSERT INTO `t _ areainfo` VALUES ('56 provinces,'0 provinces,'xx provinces 11 provinces,'1 provinces,'0'); INSERT INTO `tareainfo` VALUES ('57 provinces,'0 provinces,'xx provinces 12 provinces,'1 provinces,'0') INSERT INTO `tareainfo` VALUES ('58,'0,'xx province 13,'1,'0'); INSERT INTO `tareainfo` VALUES ('59,'0, xx province 14,'1,'0'); INSERT INTO `tareainfo` VALUES ('60,'0, xx province 15,'1,'0') INSERT INTO `tareainfo` VALUES ('61province,' 0province,'xx province 16 provinces, '1provinces,' 0'); INSERT INTO `tareainfo` VALUES ('62provinces,' 0provinces,'xx provinces 17provinces, '1provinces,' 0'); INSERT INTO `tareainfo` VALUES ('63provinces,' 0provinces,'xx provinces 18 provinces, '1provinces,' 0') INSERT INTO `t _ areainfo` VALUES ('64 provinces,'0 provinces,'xx provinces 19 provinces,'1 provinces,'0')

3. Downward recursion

Use find_in_set () function and group_concat () function to implement recursive query:

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;DELIMITER;; 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) > zero end WHILE;RETURN sTemp;END;;DELIMITER

4. Call mode

SELECT queryChildrenAreaInfo (1)

Query all nodes whose id is "4"

SELECT * FROM t_areainfo WHERE FIND_IN_SET (id,queryChildrenAreaInfo (4))

5. Upward recursion

DROP FUNCTION IF EXISTS queryChildrenAreaInfo1;DELIMITER;;CREATE FUNCTION queryChildrenAreaInfo1 (areaId INT) RETURNS VARCHAR (4000) BEGINDECLARE 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 DOSET sTemp= CONCAT (sTemp,',',sTempChd); SELECT parentId INTO sTempChd FROM t_areainfo WHERE id = sTempChd;END WHILE;RETURN sTemp;END;;DELIMITER

6. Call mode

Query all parent nodes of the node whose id is "7":

SELECT * from t_areainfo where FIND_IN_SET (id,queryChildrenAreaInfo1 (7))

After reading this article, I believe you have a certain understanding of mysql to achieve recursive query methods, want to know more related knowledge, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.