mysql method to recursively search for all child nodes of a menu node

background

There is a requirement in the project to check all nodes of the menu node. After checking online, most methods use stored procedures. Since the online environment cannot add stored procedures casually,

Therefore, a similar recursive method is used here to query all child nodes of the menu.

Prepare

Create a menu table:

CREATE TABLE `menu` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Menu id',
 `parent_id` int(11) DEFAULT NULL COMMENT 'parent node id',
 `menu_name` varchar(128) DEFAULT NULL COMMENT 'Menu name',
 `menu_url` varchar(128) DEFAULT '' COMMENT 'Menu path',
 `status` tinyint(3) DEFAULT '1' COMMENT 'Menu status 1-valid; 0-invalid',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

Insert data:

INSERT INTO `menu` VALUES ('0', null, 'Menu0', ' ', '1');
INSERT INTO `menu` VALUES ('1', '0', 'Menu 1', '', '1');
INSERT INTO `menu` VALUES ('11', '1', 'Menu 11', '', '1');
INSERT INTO `menu` VALUES ('12', '1', 'Menu 12', '', '1');
INSERT INTO `menu` VALUES ('13', '1', 'Menu 13', '', '1');
INSERT INTO `menu` VALUES ('111', '11', 'Menu 111', '', '1');
INSERT INTO `menu` VALUES ('121', '12', 'Menu 121', '', '1');
INSERT INTO `menu` VALUES ('122', '12', 'Menu 122', '', '1');
INSERT INTO `menu` VALUES ('1221', '122', 'Menu 1221', '', '1');
INSERT INTO `menu` VALUES ('1222', '122', 'Menu 1222', '', '1');
INSERT INTO `menu` VALUES ('12211', '1222', 'Menu 12211', '', '1');

The resulting directory structure is shown below:

Query

First post the sql statement:

For example, to query all child nodes of menu node 12, the query results are:

analyze

First, analyze the statements after from, sort by parent_id and id, and use the menu node to be queried as a variable. The result after from is

Next, let’s look at the if (express1, express2, express3) conditional statement. The if statement is similar to a ternary operator. When exprss1 is true, express2 is executed, otherwise express3 is executed.

FIND_IN_SET(str,strlist),str is the string to be queried,strlist field name parameters are separated by "," such as (1,2,6,8), query the results containing (str) in the field (strlist), and return the result as null or record

If parent_id is in @pid, add parent_id to @pid and execute row by row. The execution process is shown in the following table:

At this time, the displayed id is all the child node ids of the menu id 12

This is the end of this article about mysql recursive search for all child nodes of a menu node. For more relevant mysql recursive search for menu nodes content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!