mysql 无限极递归_mysql8 递归 实现无限极分类

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

之前实现无限极分类用的是在代码层实现递归查询，听说mysql8 对递归查询很友好，正好遇到这个功能，就网上搜资料实现了一个简单的功能，确实性能很好，由之前的0.4秒提升到 0.01秒左右

实现的效果 ：

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 FROMtype_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 FROMtype_cte ORDER BY id ASC

tp： 查询父类或子类(包含本类)

echo '递归

';

echo '

所有子类

';

$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 '所有父类

';

$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);