MySQL数据库递归语句

– 创建 树状结构表

CREATE TABLE `tb_tree` (
  `id`                          BIGINT          UNSIGNED    NOT NULL 	                COMMENT '主键ID',
  `code`                        VARCHAR(50)     NOT NULL                                COMMENT '编码',
  `name`                        VARCHAR(50)     NOT NULL                                COMMENT '名称',
  `parent_id`                   BIGINT                                                  COMMENT '父级ID',
  `parent_code`                 VARCHAR(50)                                             COMMENT '父级编码',
  `parent_name`                 VARCHAR(50)                                             COMMENT '父级名称',
  `level`                       INT                                                     COMMENT '层级',
  `level_name`                  VARCHAR(500)                                            COMMENT '层级名称',
  `sequence`                    INT             NOT NULL    DEFAULT '99'                COMMENT '排序 正序 默认 99',

  `data_status`                 TINYINT         NOT NULL    DEFAULT '1'                 COMMENT '数据状态 0 草稿，1 可用，-1 删除，默认 1',
  `creator`                     VARCHAR(50)                                             COMMENT '创建者ID',
  `creator_name`                VARCHAR(50)                                             COMMENT '创建者名称',
  `create_time`                 BIGINT                                                  COMMENT '创建时间（时间戳）',
  `updater`                     VARCHAR(50)                                             COMMENT '最后更新者ID',
  `updater_name`                VARCHAR(50)                                             COMMENT '最后更新者名称',
  `update_time`                 BIGINT                                                  COMMENT '最后更新时间（时间戳）',
  `remark`                      VARCHAR(500)                                            COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET='utf8' COLLATE='utf8_general_ci' COMMENT='树状结构表';

– 导入数据

INSERT  INTO `tb_tree`(`id`,`code`,`name`,`parent_id`,`parent_code`,`parent_name`,`level`,`level_name`,`sequence`,`data_status`,`creator`,`creator_name`,`create_time`,`updater`,`updater_name`,`update_time`,`remark`) VALUES 
(1,'root_node_1','根节点1',NULL,NULL,NULL,1,'一级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(2,'root_node_2','根节点2',NULL,NULL,NULL,1,'一级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(3,'root_node_3','根节点3',NULL,NULL,NULL,1,'一级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(4,'child_node_1','子节点1',1,'root_node_1','根节点1',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(5,'child_node_2','子节点2',1,'root_node_1','根节点1',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(6,'child_node_3','子节点3',1,'root_node_1','根节点1',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(7,'child_node_4','子节点4',2,'root_node_2','根节点2',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(8,'child_node_5','子节点5',2,'root_node_2','根节点2',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(9,'child_node_6','子节点6',2,'root_node_2','根节点2',2,'二级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(10,'child_node_7','子节点7',4,'child_node_1','子节点1',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(11,'child_node_8','子节点8',4,'child_node_1','子节点1',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(12,'child_node_9','子节点9',4,'child_node_1','子节点1',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(13,'child_node_10','子节点10',5,'child_node_2','子节点2',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(14,'child_node_11','子节点11',5,'child_node_2','子节点2',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(15,'child_node_12','子节点12',5,'child_node_2','子节点2',3,'三级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(16,'child_node_13','子节点13',10,'child_node_7','子节点7',4,'四级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(17,'child_node_14','子节点14',10,'child_node_7','子节点7',4,'四级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
(18,'child_node_15','子节点15',10,'child_node_7','子节点7',4,'四级',99,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

– 迭代查询（从根节点往下查询）

SELECT DATA.level,id._ids, DATA.* FROM(
    -- 递归核心，查询所有层级ID
	SELECT
	@ids AS _ids, -- 当前层级所有ID
	(
		SELECT
		@ids := GROUP_CONCAT(id)  -- 递归核心，拼接子ID，重新赋值
		FROM tb_tree
		WHERE FIND_IN_SET(parent_id, @ids) -- 递归核心，当前层级ID当做父级ID查询
	) AS cids  -- 当前层级所有子ID
	FROM tb_tree, (SELECT @ids := '1' ) b -- 初始根节点ID
	WHERE @ids IS NOT NULL -- 无数据则退出
) id, tb_tree DATA
WHERE FIND_IN_SET(DATA.id, id._ids)
ORDER BY DATA.level,DATA.id

– 迭代查询（从子节点往上查询）

SELECT DATA.level,id._id, DATA.* FROM (
    -- 递归核心，查询所有层级ID
	SELECT
	@id AS _id, -- 当前层级ID
	(
		SELECT
		@id := parent_id -- 递归核心，重新赋值
		FROM tb_tree
		WHERE id = @id  -- 递归核心，将父级ID 换为 数据主键
	) AS pid -- 当前层级父ID
	FROM tb_tree, (SELECT @id := 5 ) b -- 初始子节点ID
	WHERE @id IS NOT NULL -- 无数据则退出
) id, tb_tree DATA
WHERE DATA.id = id._id
ORDER BY DATA.level