mysql5.7 sql语句实现父子查询

1.数据表创建

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `goods_metas`;
CREATE TABLE `goods_metas`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `name` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名',
  `type` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '类型',
  `count` int(11) NULL DEFAULT 0 COMMENT '统计',
  `parent` int(11) NULL DEFAULT 0 COMMENT '父编号（只有class需要）',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`, `type`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  
INSERT INTO `goods_metas` VALUES (1, '美甲', 'class', 0, 0);
INSERT INTO `goods_metas` VALUES (2, '长甲', 'class', 0, 1);
INSERT INTO `goods_metas` VALUES (3, '短甲', 'class', 0, 1);
INSERT INTO `goods_metas` VALUES (4, '可爱', 'tag', 0, 0);
INSERT INTO `goods_metas` VALUES (5, '简约', 'tag', 0, 0);
INSERT INTO `goods_metas` VALUES (6, '测试', 'class', 0, 2);
SET FOREIGN_KEY_CHECKS = 1;

2.根据父id查所有子节点

将{id}修改为查询的父id
不包含父节点自身

select * from goods_metas where id in (SELECT id 
FROM (SELECT * FROM goods_metas where type="class") goods_metas,
     (SELECT @pid := '{id}') pd 
WHERE FIND_IN_SET(parent, @pid) > 0 
  AND @pid := concat(@pid, ',', id));

id=0查询结果

+----+----------+-------+-------+--------+
| id | name     | type  | count | parent |
+----+----------+-------+-------+--------+
|  2 | 长甲     | class |     0 |      1 |
|  3 | 短甲     | class |     0 |      1 |
|  6 | 测试     | class |     0 |      2 |
+----+----------+-------+-------+--------+