mysql5.7 sql语句实现父子查询 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 | +----+----------+-------+-------+--------+