mysql8 递归 实现无限极分类

根据id 查询所有的父类或者子类（不包含本类）

实现的效果 ：

 

 

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for digui
-- ----------------------------
DROP TABLE IF EXISTS `digui`;
CREATE TABLE `digui` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of digui
-- ----------------------------
INSERT INTO `digui` VALUES ('1', '手机', '0');
INSERT INTO `digui` VALUES ('2', '电脑', '0');
INSERT INTO `digui` VALUES ('3', '家居', '0');
INSERT INTO `digui` VALUES ('4', '美妆', '0');
INSERT INTO `digui` VALUES ('5', '运营商', '1');
INSERT INTO `digui` VALUES ('6', '数码', '5');
INSERT INTO `digui` VALUES ('7', '办公', '2');
INSERT INTO `digui` VALUES ('8', '家具', '3');
INSERT INTO `digui` VALUES ('9', '家装', '8');
INSERT INTO `digui` VALUES ('10', '厨具', '9');
INSERT INTO `digui` VALUES ('11', '个护清洁', '4');
INSERT INTO `digui` VALUES ('12', '宠物', '4');
INSERT INTO `digui` VALUES ('13', '存储卡', '6');
INSERT INTO `digui` VALUES ('14', '三脚架', '6');
INSERT INTO `digui` VALUES ('15', '家纺', '3');
INSERT INTO `digui` VALUES ('16', '收纳用品', '3');

 

-- 查询所有子类
 WITH RECURSIVE type_cte AS (
    SELECT * FROM digui WHERE pid = 3
    UNION ALL SELECT t.* FROM digui t INNER JOIN type_cte type_cte2 ON t.pid = type_cte2.id
) SELECT id,name,pid FROM	type_cte ORDER BY id ASC;

-- 查询所有父类 
 WITH RECURSIVE type_cte AS (
    SELECT * FROM digui WHERE id = 8
    UNION ALL SELECT t.* FROM digui t INNER JOIN type_cte type_cte2 ON t.id = type_cte2.pid
) SELECT id,name,pid FROM	type_cte ORDER BY id ASC

 

 

 

tp： 查询父类或子类（包含本类）

        echo '递归<br/>';

        echo '<br/>所有子类<br/>';
        $res = Db::query('WITH RECURSIVE type_cte AS (
                SELECT * FROM digui WHERE id = ?
                UNION ALL SELECT t.* FROM digui t INNER JOIN type_cte type_cte2 ON t.pid = type_cte2.id
            ) SELECT id,name FROM type_cte order by id asc',[5]);
        $ids = array_column($res,'id');
        dump($res);

        echo '所有父类<br/>';
        $res = Db::query('WITH RECURSIVE type_cte AS (
                SELECT * FROM digui WHERE id = ?
                UNION ALL SELECT t.* FROM digui t INNER JOIN type_cte type_cte2 ON t.id = type_cte2.pid
            ) SELECT id,name FROM type_cte order by id asc ',[5]);
        $anmes = array_column($res,'name');
        $anmes = implode('/',$anmes);
        dump($anmes);