How to query child nodes in mysql-Mysql Tutorial-php.cn

How to query child nodes in mysql

coldplay.xixi

Release： 2020-10-29 16:15:15

Original

2474 people have browsed it

Mysql method of querying child nodes: first create the menu table and insert data; then use the statement to query, the code is [select id from(select t1.id,if(find_in_set(parent_id, @pids) > ; 0...)】.

How to query child nodes in mysql

More related free learning recommendations: mysql tutorial(Video)

Mysql method of querying child nodes:

Create menu table:

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT &#39;菜单id&#39;,
  `parent_id` int(11) DEFAULT NULL COMMENT &#39;父节点id&#39;,
  `menu_name` varchar(128) DEFAULT NULL COMMENT &#39;菜单名称&#39;,
  `menu_url` varchar(128) DEFAULT &#39;&#39; COMMENT &#39;菜单路径&#39;,
  `status` tinyint(3) DEFAULT &#39;1&#39; COMMENT &#39;菜单状态 1-有效；0-无效&#39;,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12212 DEFAULT CHARSET=utf8;

Copy after login

Insert data:

INSERT INTO `menu` VALUES (&#39;0&#39;, null, &#39;菜单0&#39;, &#39; &#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;1&#39;, &#39;0&#39;, &#39;菜单1&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;11&#39;, &#39;1&#39;, &#39;菜单11&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;12&#39;, &#39;1&#39;, &#39;菜单12&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;13&#39;, &#39;1&#39;, &#39;菜单13&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;111&#39;, &#39;11&#39;, &#39;菜单111&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;121&#39;, &#39;12&#39;, &#39;菜单121&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;122&#39;, &#39;12&#39;, &#39;菜单122&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;1221&#39;, &#39;122&#39;, &#39;菜单1221&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;1222&#39;, &#39;122&#39;, &#39;菜单1222&#39;, &#39;&#39;, &#39;1&#39;);
INSERT INTO `menu` VALUES (&#39;12211&#39;, &#39;1222&#39;, &#39;菜单12211&#39;, &#39;&#39;, &#39;1&#39;);

Copy after login

The resulting directory structure is as shown below:

How to query child nodes in mysql

Query Issue the sql statement:

select id from (
              select t1.id,
              if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, &#39;,&#39;, id), 0) as ischild
              from (
                   select id,parent_id from re_menu t where t.status = 1 order by parent_id, id
                  ) t1,
                  (select @pids := 要查询的菜单节点 id) t2
             ) t3 where ischild != 0

Copy after login

For example, if you want to query all the child nodes of menu node 12, the result of the investigation is:

The above is the detailed content of How to query child nodes in mysql. For more information, please follow other related articles on the PHP Chinese website!