Mysql counterattack: how to perform recursive hierarchical queries

Mysql: how to perform recursive hierarchical queries is a migration from the oracle database to the mysql database recently, in Oracle, we know that there is a Hierarchical Queries. Using connect by, we can easily query all the subnodes under all the current nodes. However, shi, there are no corresponding functions in the current MySQL version !!! In other words, if you want to use mysql for Recursive queries, you cannot do it !!! However, after several days of hard work, I finally thought of a reasonable solution for mysql and other SQL. When the www.2cto.com solution was launched, the entire dao layer was swept away from the fallen leaves ~~~ Everywhere, all problems are solved, so that all problems are no longer the cannon fodder of my function... Let's talk about the solution ~~~~~ The following is an SQL script. If you want to run the script and copy the paste below, run treenodes. sq directly... /* Navicat MySQL Data Transfer Source Server: mysql_demo3 Source Server Version: 50521 Source Host: localhost: 3306 Source Database: test Target Server Type: MYSQL Target Server Version: 50521 File Encoding: 65001 www.2cto.com Date: 2012-09-02 21:16:03 */SET FOREIGN_KEY_CHECKS = 0; -- -------------------------- -- Table structure for 'treenodes '-- ------------------------------ DROP TABLE IF EXISTS 'treenodes '; create table 'treenodes' ('id' int (11) not null, 'nodename' varchar (20) default null, 'pid' int (11) default null, primary key ('id') ENGINE = InnoDB default charset = latin1; -- -------------------------- -- Records of treenodes -- ignore insert into 'treenodes 'values ('1 ', 'A', '0'); insert into 'treenodes 'values ('2', 'B', '1'); INSERT INTO 'treenode S 'values ('3', 'C', '1'); insert into 'treenodes 'values ('4', 'D', '2 '); insert into 'treenodes 'values ('5', 'E', '2'); insert into 'treenodes 'values ('6', 'F', '3 '); insert into 'treenodes 'values ('7', 'G', '6'); insert into 'treenodes 'values ('8', 'h', '0 '); insert into 'treenodes 'values ('9', 'I', '8'); INSERT INTO 'treenodes 'values ('10', 'J', '8 '); insert into 'treenodes 'values ('11', 'K', '8'); insert into 'treenodes 'values ('12', 'l', '9'); insert into 'treenodes 'values ('13 ', 'M', '9'); insert into 'treenodes 'values ('14', 'n', '12'); insert into 'treenodes 'values ('15 ', 'o', '12'); insert into 'treenodes 'values ('16', 'P', '15'); insert into 'treenodes 'values ('17 ', 'Q', '15'); www.2cto.com --------------------------------------------------- the preceding SQL script is displayed after the select * command is executed. The result set is as follows: mysql> select * from treenodes; + ---- + ---------- + ------ + | id | nodename | pid | + ---- + ---------- + ------ + | 1 | A | 0 | 2 | B | 1 | 3 | C | 1 | 4 | D | 2 | 5 | E | 2 | 6 | F | 3 | 7 | G | 6 | 8 | H | 0 | 9 | I | 8 | 10 | J | 8 | 11 | K | 8 | 12 | L | 9 | 13 | M | 9 | 14 | N | 12 | 15 | O | 12 | 16 | P | 15 | 17 | Q | 15 | + ---- + ---------- + ------ + 17 rows The in set (0.00 sec) tree contains the following 1: A + -- 2: B | + -- 4: D | + -- 5: E + -- 3: C + -- 6: F + -- 7: G 8: H + -- 9: I | + -- 12: L | + -- 14: N | + -- 15: O | + -- 16: P | + -- 17: Q | + -- 13: M + -- 10: J + -- 11: K -------------------------------------------- if you give you a table like this, let you query the records of all nodes with the root node being 1 (note that the root node is also included). How can this problem be solved ????? Many may think of the connect by function, but I'm sorry to tell you that mysql is here !!! Well, objectively speaking, my solution is to use functions to get all the subnode numbers. If you have less time to continue, see my solution to create a function getChildLst to get a string consisting of all the child node numbers. mysql> delimiter // mysql> create function 'getchildlst' (rootId INT)-> RETURNS varchar (1000)-> BEGIN-> DECLARE sTemp VARCHAR (1000 ); -> DECLARE sTempChd VARCHAR (1000);-> SET sTemp = '$';-> SET sTempChd = cast (rootId as CHAR ); -> WHILE sTempChd is not null DO-> SET sTemp = concat (sTemp, ',', sTempChd);-> SELECT group_concat (I D) INTO sTempChd FROM treeNodes where FIND_IN_SET (pid, sTempChd)> 0;-> end while;-> RETURN sTemp;-> END-> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter; www.2cto.com we use the find_in_set function directly with this getChildlst to find mysql> select getChildLst (1 ); + ----------------- + | getChildLst (1) | + ----------------- + | $, 0.00, | + ----------------- + 1 row in set (sec) mysql> select * from TreeNodes-> where FIND_IN_SET (id, getChildLst (1 )); + ---- + ---------- + ------ + | id | nodename | pid | + ---- + ---------- + ------ + | 1 | A | 0 | 2 | B | 1 | 3 | C | 1 | 4 | D | 2 | 5 | E | 2 | 6 | F | 3 | 7 | G | 6 | + ---- + ---------- + ------ + 7 rows in set (0.01 sec) mysql> select * from treeNodes-> where FIND_IN_SET (id, getChildLst (3); + ---- + ---------- + ------ + | id | nodename | pid | + -- -- + ---------- + ------ + | 3 | C | 1 | 6 | F | 3 | 7 | G | 6 | + ---- + ---------- + ------ + 3 rows in set (0.01 sec) -------------------------------------------- as long as you press my work, all in all out of the box, if you encounter problems, paste and copy them directly... Supplement: You can also perform nested queries: select id, pid from treeNodes where id in (select id from treeNodes where FIND_IN_SET (id, getChildLst (3 ))); the result set of the subquery is www.2cto.com + -------- + id ---- 3 6 7 + ------- +. Then, after the outer query, the result set is id pid 3 1 6 6 6 --------- Perfect.