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

This article is about how MySQL implements the query tree structure. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

MySQL query tree structure 1. About tree structure

Data of this kind of structure usually needs to contain self-association fields such as id and parentId in the table structure. Sometimes, in order to improve query efficiency, more redundant fields can be added, such as index. The value of index is the id string collection of all parent directories.

About tree structure data assembly, common writing is in the program through recursive way to build a complete tree, simply through sql way is not commonly used, the following two ways are given examples.

2. MySQL custom functions

What is MySQL custom function: aggregate function, date function and so on are MySQL functions, here we define the function can be used as they are, but only in the defined database, custom functions and stored procedures are similar, the difference is that the function will only return a value, not allowed to return a result set.

2.1 Creating Test Data CREATE TABLE `tree`( `id` bigint(11) NOT NULL, `pid` bigint(11) NULL DEFAULT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; INSERT INTO `tree` VALUES (1, 0, 'China');INSERT INTO `tree` VALUES (2, 1, 'Sichuan Province');INSERT INTO `tree` VALUES (3, 2, 'Chengdu City');INSERT INTO `tree` VALUES (4, 3, 'Wuhou District');INSERT INTO `tree` VALUES (5, 4, 'red archway');INSERT INTO `tree` VALUES (6, 1, 'Guangdong Province');INSERT INTO `tree` VALUES (7, 1, 'Zhejiang Province');INSERT INTO `tree` VALUES (8, 6, 'Guangzhou City');2.2 Obtain all child nodes under a node CREATE FUNCTION `GET_CHILD_NODE`(rootId varchar(100)) RETURNS varchar(2000) BEGIN DECLARE str varchar(2000); DECLARE cid varchar(100); SET str = '$'; SET cid = rootId; WHILE cid is not null DO SET str = concat(str, ',', cid); SELECT group_concat(id) INTO cid FROM tree where FIND_IN_SET(pid, cid); END WHILE; RETURN str; END

Call custom functions

select * from tree where FIND_IN_SET(id, GET_CHILD_NODE(2));

2.3 GET_PARENT_NODE (rootId varchar(100)) RETURNS varchar(1000) BEGIN DECLARE fid varchar(100) default ''; DECLARE str varchar(1000) default rootId; WHILE rootId is not null do SET fid =(SELECT pid FROM tree WHERE id = rootId); IF fid is not null THEN SET str = concat(str, ',', fid); SET rootId = fid; ELSE SET rootId = fid; END IF; END WHILE; return str; END

Call custom functions

select * from tree where FIND_IN_SET(id, GET_PARENT_NODE(5));

3. Oracle Database

Just use the start with connect by prior statement to complete the recursive tree query, please refer to the relevant information for details.

4. The recursive way program code builds trees

I won't give you the complete code here. The recursive method is very simple. It is to find all tree nodes first, and then add all child nodes recursively through the add method in a TreeNode class. The core code is as follows:

public class TreeNodeDTO { private String id; private String parentId; private String name; private List children = new ArrayList(); public void add(TreeNodeDTO node) { if ("0".equals(node.parentId)) { this.children.add(node); } else if (node.parentId.equals(this.id)) { this.children.add(node); } else { //recursively call add() to add child nodes for (TreeNodeDTO tmp_node : children) { tmp_node.add(node); } } } }5. With hashMap, you only need to traverse once

You can complete the tree generation: five-star recommendation

List list = dbMapper.getNodeList();ArrayList rootNodes = new ArrayList();Map map = new HashMap();for (TreeNodeDTO node :list) { map.put(node.getId(), node); Integer parentId = node.getParentId(); //determine whether there is a parent node (no parent node itself is a parent menu) if (parentId.equals('0')){ rootNodes.add(node); //Find a menu that is not a parent menu and includes its parent menu ID in the collection } else if (map.containsKey(parentId)){ map.get(parentId).getChildren().add(node); }}MySQL queries information with tree structure

In Oracle, there are function applications that can directly query tree structure information, such as the following tree structure organization member architecture, so if we want to query all node information under one of the nodes

In Oracle, you can query START WITH CONNECT BY PRIOR directly with the following syntax, but there is no such syntax in Mysql.

And what if you want to query this data structure information? We can customize functions. We initialize the above information into the database. First, create a table to store this information. ID stores ID information of itself. PARENT_ID stores ID information of parent.

CREATE TABLE `company_inf` ( `ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `NAME` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `PARENT_ID` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL)

Then initialize the information in the diagram into the table

INSERT INTO company_inf VALUES ('1 ',' General Manager Wang Dazi','1');INSERT INTO company_inf VALUES ('2 ',' R & D Manager Liu Da Cripple','1');INSERT INTO company_inf VALUES ('3 ',' Sales ManagerMa Erlangzi','1');INSERT INTO company_inf VALUES ('4 ',' Finance Manager Zhao Santuozi','1');INSERT INTO company_inf VALUES ('5 ',' Secretary Employee J','1');INSERT INTO company_inf VALUES ('6 ',' R & D Team Leader Wu Dabangchui','2');INSERT INTO company_inf VALUES ('7 ',' R & D Group II Leader Zheng Laoliu','2');INSERT INTO company_inf VALUES ('8 ',' Salesperson G','3');INSERT INTO company_inf VALUES ('9 ',' Salesman H','3');INSERT INTO company_inf VALUES ('10 ',' FinancierI','4');INSERT INTO company_inf VALUES ('11 ',' Developer A','6');INSERT INTO company_inf VALUES ('12 ',' Developer B','6');INSERT INTO company_inf VALUES ('13',' Developer C','6');INSERT INTO company_inf VALUES ('14','Developer D',' 7');INSERT INTO company_inf VALUES ('15 ',' Developer E','7');INSERT INTO company_inf VALUES ('16','Developer F',' 7');

For example, if we want to query all employees under R & D department manager Liu Da cripple, we can write this in Oracle.

SELECT * FROM T_PORTAL_AUTHORITY START WITH ID='1' CONNECT BY PRIOR ID = PARENT_ID

In Mysql we need custom functions like this

CREATE FUNCTION getChild(parentId VARCHAR(1000))RETURNS VARCHAR(1000)BEGIN DECLARE oTemp VARCHAR(1000); DECLARE oTempChild VARCHAR(1000); SET oTemp = ''; SET oTempChild =parentId; WHILE oTempChild is not null DO IF oTemp != '' THEN SET oTemp = concat(oTemp,',',oTempChild); ELSE SET oTemp = oTempChild; END IF; SELECT group_concat(ID) INTO oTempChild FROM company_inf where parentIdID and FIND_IN_SET(parent_id,oTempChild)>0; END WHILE;RETURN oTemp;END

Then you can query like this

SELECT * FROM company_inf WHERE FIND_IN_SET(ID,getChild('2'));

At this time, check the information that comes out of the query is all the employee information under Liu Da Cripple.

Thank you for reading! About "MySQL how to achieve query tree structure" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.