mysql - 根据ID删除全部子类

业务场景

做产品分类表的时候，通常会有这样的设计：一个字段是 ID，另一个字段 PID，PID 指向自己的上级分类；
这样的设计带来的问题是：删除全部子类是非常麻烦的一件事情。

优化方案

在设计表之初，增加一个辅助字段 path，令 path 记住自己的 id 路径即可解决这个问题。

以上图为例：
电视的 ID 是 1，它的 PID 是 0（表示没有父类），
海尔电视的 ID 是 2，海尔电视的 PID 是 1，说明是电视的子类；
这时候电视的 path 就是 "0"，海尔电视的 path 就是 "0,1"。

在查询的时候，使用 mysql 自带的 FIND_IN_SET() 函数即可。

-- 可以先试试 FIND_IN_SET() 看看效果
SELECT FIND_IN_SET('b','a,b,c,d');

CREATE TABLE `cls` (
  `id` int unsigned NOT NULL COMMENT 'ID',
  `pid` int unsigned DEFAULT NULL COMMENT '父级ID',
  `path` varchar(10) DEFAULT NULL COMMENT '路径',
  `name` varchar(10) DEFAULT NULL COMMENT '名称',
  `level` int DEFAULT NULL COMMENT '层级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


INSERT INTO `med`.`cls` (`id`, `pid`, `path`, `name`, `level`) VALUES ('1', '0', '0', '电视', '1');
INSERT INTO `med`.`cls` (`id`, `pid`, `path`, `name`, `level`) VALUES ('2', '1', '0,1', '海尔电视', '2');
INSERT INTO `med`.`cls` (`id`, `pid`, `path`, `name`, `level`) VALUES ('3', '1', '0,1', 'TCL电视', '2');

-- 查找所有电视的子类
SELECT * FROM `cls` AS T WHERE FIND_IN_SET('0',T.path);

进阶优化

上述方案存在一个问题，就是随着层级增加，path 字段会越来越长， 如果想要优化，可以参考邮政编码的做法。

例如：350700，350 是福建邮编的开头，查询福建的邮件，只要判断是不是 350 开头即可。
邮政编码会增加一定维护成本，在新增和修改的时候，需要定好邮政编码，才能确保规则的进行。