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

Home

Database

Mysql Tutorial

How to query child nodes in mysql

coldplay.xixi

Oct 27, 2020 pm 03:36 PM

mysql

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;

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;);

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

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!

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn