The method of realizing Recursive query by mysql
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!
