Shulou(Shulou.com)05/31 Report--

This article is about how to achieve recursive query in MySQL. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article. Let's follow the editor to have a look.

Oracle recursive query

In Oracle, recursive queries are implemented through start with connect by prior syntax.

There are four cases according to whether the prior keyword is on the child node side or the parent node side, and whether the node of the current query is included.

Prior at the end of the child node (recursive downward)

The first case: start with child node id = 'query node' connect by prior child node id = parent node id

Select * from dept start with id='1001' connet by prior id=pid

Here, the current node and its child nodes are queried recursively according to the condition id='1001'. The query result contains itself and all child nodes.

The second case: start with parent node id= 'query node' connect by prior child node id= parent node id

Select * from dept start with pid='1001' connect by prior id=pid

Here, all the children of the current node are queried recursively according to the condition pid='1001'. The query result contains only all its child nodes, not itself.

In fact, it is right to think about it, because the starting condition is to take the parent node as the root node, and recursive downward, naturally does not include the current node.

Prior at the end of the parent node (recursive upward)

The third case: start with child node id= 'query node' connect by prior parent node id= child node id

Select * from dept start with id='1001' connect by prior pid=id

Here, according to the condition id='1001', the current node and its parent node are queried recursively. The query result includes itself and all its parent nodes.

The fourth case: start with parent node id= 'query node' connect by prior parent node id= child node id

Select * from dept start with pid='1001' connect by prior pid=id

Here, according to the condition pid='1001', the first generation child node of the current node and its parent node are recursively queried. The query results include their own first-generation child nodes and all parent nodes. (including yourself)

In fact, this situation is also easy to understand, because the starting condition of the query is based on the parent node and recursive upward, so it is naturally necessary to include the first-level child nodes of the current parent node.

At first glance, the above four situations may be confusing and easy to remember, but they are not.

We just need to remember that the location of the prior is at the end of the child node, recursive downward, and recursive upward at the end of the parent node.

If the starting condition is a child node, it naturally includes its own node.

If the starting condition is the parent node, then the current node is not included in the downward recursion. The upward recursion needs to include the current node and its first-generation child nodes.

MySQL recursive query

As you can see, it is very convenient for Oracle to implement recursive queries. However, it is not handled for us in MySQL, so we need to manually implement the recursive query ourselves.

For convenience, we create a department table and insert several pieces of data that can form a recursive relationship.

DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` (`id` varchar (10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `name` varchar (255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `pid` varchar (10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1000,' head office', NULL) INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1001,' Beijing Branch, '1000'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1002,' Shanghai Branch, '1000'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1003, Beijing R & D Department', '1001'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1004, Beijing Finance Department,' 1001') INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1005,' Beijing Marketing Department, '1001'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1006,' Beijing R & D 1', '1003'); INSERT INTO `dept` (`1007, `name`, `pid`) VALUES ('1007,' Beijing R & D second', '1003'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1008,' Beijing R & D team', '1006') INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('10094th,' Beijing R & D No.2 team', '1006'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES (' 1010', 'Beijing R & D second team', '1007'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES (' 1011efficiency, 'Beijing R & D second team', '1007') INSERT INTO `dept` (`id`, `name`, `pid`) VALUES ('1012,' Beijing Market one', '1005'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES (' 1013,`Shanghai R & D Department', '1002'); INSERT INTO `dept` (`1014,`name`, `pid`) VALUES (' 1014mm, Shanghai R & D No.1', '1013'); INSERT INTO `dept` (`id`, `name`, `pid`) VALUES (' 1015mm, 'Shanghai R & D Department', '1013')

Yes, just now Oracle recursion, is using this table.

Figure 1

In addition, before we do that, we need to review a few functions in MYSQL, which we will use later.

Find_in_set function

Function syntax: find_in_set (str,strlist)

Str represents the string to be queried, and strlist is a comma-separated string, such as ('a _ dint _ b _ c').

This function is used to find the position of the str string in the string strlist and returns a result of 1 ~ n. If it is not found, 0 is returned.

Take a chestnut:

Select FIND_IN_SET ('bachelors, pas, c,)

The result returns 2. Because the position of b is the second substring position.

In addition, it has a use when querying table data, as follows:

Select * from dept where FIND_IN_SET (id,'1000,1001,1002')

The result returns all id records in strlist, that is, id = '1000', id =' 1001', and id = '1002'.

Seeing this, I don't know if you have any inspiration for the recursive query we want to solve.

Take, for example, a downward recursive query for all child nodes. I wonder if it is possible to find a comma concatenated string strlist containing the current node and all child nodes and pass it into the find_in_set function. You can query all the required recursive data.

So, now the question turns to how to construct such a string strlist.

This requires the use of the following string concatenation function.

Concat,concat_ws,group_concat

Function 1, string concatenation function, the most basic is concat. It is used to concatenate N strings, such as

Select CONCAT ('Mauremen.Yandongjinyangjinghuangyuanzhongyuanzhongyuanshou') from dual.

The result is a 'MYSQL' string.

Second, concat takes a comma as the default delimiter, while concat_ws can specify the delimiter. The first parameter is passed a delimiter, such as an underscore delimiter.

Third, the group_concat function is more powerful, which can group fields into strings with specific delimiters at the same time.

Usage: group_concat ([distinct] the field to be connected [order by sort field asc/desc] [separator 'delimiter'])

You can see that there are optional parameters, you can de-duplicate the values of the fields to be spliced, or you can sort them and specify delimiters. If not specified, it is separated by a comma by default.

For the dept table, we can concatenate all the id in the table with commas. (if the group by grouping field is not used here, only one group can be considered.)

MySQL custom function to implement recursive query

It can be found that the above problem of string concatenation has also been solved. Then the problem becomes how to construct a string with a recursive relationship.

We can customize a function to find all its children by passing in the root node id.

Take downward recursion as an example. (explain the writing of custom functions and recursive logic at the same time)

Delimiter $$drop function if exists get_child_list$$ create function get_child_list (in_id varchar (10)) returns varchar (1000) begin declare ids varchar (1000) default''; declare tempids varchar (1000); set tempids = in_id; while tempids is not null do set ids = CONCAT_WS (',', ids,tempids); select GROUP_CONCAT (id) into tempids from dept where FIND_IN_SET (pid,tempids) > 0; end while; return ids; end $delimiter

(1) delimiter $$, which defines the Terminator. We know that the default Terminator for MySQL is a semicolon, indicating that the instruction ends and executes. But in the function body, sometimes we want to encounter that the semicolon does not end, so we need to temporarily change the Terminator to a random other value. I set it to $$here, which means it ends when it encounters $$and executes the current statement.

(2) drop function if exists get_child_list$$. If the function get_child_list already exists, delete it first. Note that you need to end and execute the statement with the current custom Terminator $$. Because, here, you need to separate the number from the function body below.

(3) create function get_child_list creates the function. And the parameter is passed into the id of a root node, be sure to indicate the type and length of the parameter, for example, varchar (10). Returns varchar (1000) is used to define the return value parameter type.

(4) what is surrounded between begin and end is the function body. Used to write specific logic.

(5) declare is used to declare variables, and the default value can be set with default.

The ids defined here is used as the return value of the entire function and is used to concatenate the comma-separated recursive string that we finally need.

Tempids is a string concatenated by commas to record all temporarily generated child nodes in the following while loop.

(6) set is used to assign values to variables. Here the root node passed in is assigned to the tempids.

(7) while do... End while; loop statement, which contains loop logic. Note that a semicolon is required at the end of the end while.

In the loop body, the final result ids and the temporarily generated tempids are concatenated with a comma using the CONCAT_WS function.

Then, under the condition of FIND_IN_SET (pid,tempids) > 0, we traverse all the pid in the tempids to find all the child nodes id with this as the parent node, and use the GROUP_CONCAT (id) into tempids to concatenate these child nodes with commas, and overwrite the updated tempids.

The next time you loop in, the ids will be spliced again and all the children of all child nodes will be found again. Recursively, layer by layer recursively traverses the child nodes. Until it is determined that the tempids is empty, indicating that all the child nodes have been traversed, the entire loop is ended.

Here, take '1000' as an example, that is: (see the table data relationship in figure 1)

First loop: tempids=1000 ids=1000 tempids=1001,1002 (all child nodes of 1000) second loop: tempids=1001,1002 ids=1000,1001,1002 tempids=1003,1004,1005,1013 (all child nodes of 1001 and 1002) third loop: tempids=1003,1004,1005,1013 ids=1000,1001,1002,1003,1004,1005,1013 tempids=1003 and all child nodes of 1004,1005 and 1002. The last loop ends the loop because the child node, tempids=null, cannot be found.

(8) return ids; is used to return ids as a function return value.

(9) after the function body ends, remember to use the Terminator $$to end the entire logic and execute.

Finally, don't forget to reset the Terminator to the default Terminator semicolon.

Once the custom function is done, we can use it to recursively query the data we need. For example, I query all the child nodes of the Beijing R & D department.

The above is a recursive query for all child nodes, including the current node, or you can change the logic to not include the current node, so I won't demonstrate it.

Manually implement recursive queries (upward recursion)

Compared to downward recursion, upward recursion is relatively simple.

Because when recursive downward, a parent node in each layer corresponds to multiple child nodes.

In the case of upward recursion, one child node in each layer recursively corresponds to only one parent node, and the relationship is relatively simple.

Similarly, we can define a function get_parent_list to get all the parent nodes of the root node.

Delimiter $$drop function if exists get_parent_list$$ create function get_parent_list (in_id varchar (10)) returns varchar (1000) begin declare ids varchar (1000); declare tempid varchar (10); set tempid = in_id; while tempid is not null do set ids = CONCAT_WS (',', ids,tempid); select pid into tempid from dept where id=tempid; end while; return ids; end $$delimiter

Find the first team of Beijing R & D Department, as well as its recursive parent node, as follows:

Matters needing attention

We use the group_concat function to concatenate strings. However, it should be noted that it has a length limit, which defaults to 1024 bytes. You can view it through show variables like "group_concat_max_len";.

Note that the units are bytes, not characters. In MySQL, a single letter accounts for 1 byte, while in our usual utf-8, a Chinese character accounts for 3 bytes.

This is also very fatal for recursive queries. Because in general recursion, the relationship level is relatively deep and is likely to exceed the maximum length. (although generally concatenated is a numeric string, that is, a single byte)

Therefore, we have two ways to solve this problem:

Modify the MySQL configuration file my.cnf to increase group_concat_max_len = 102400 # the maximum length you want.

Execute any of the following statements. SET GLOBAL group_concat_max_len=102400; or SET SESSION group_concat_max_len=102400

The difference between them is that global is global, and any opening of a new session will take effect, but note that the current session that is already open will not. While session will only take effect in the current session, other sessions will not take effect.

What they have in common is that they all fail after the MySQL is restarted, depending on the configuration in the configuration file. Therefore, it is recommended that you modify the configuration file directly. A length of 102400 is generally enough. Assuming that an id is 10 bytes long, it can also spell 10, 000 id.

In addition, another limitation of using the group_concat function is that you cannot use limit at the same time. Such as

Originally, I only wanted to check 5 pieces of data for stitching, but now it doesn't work.

However, if necessary, you can do this through a subquery

The above is how to achieve recursive query in MySQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.