– 创建 树状结构表
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