MySQL技巧：无限级自定义分类

作者: yuanzicheng | 来源:发表于2017-05-12 18:33 被阅读1730次

MySQL技巧：无限级自定义分类
MySQL 冷门技术总结
无限级分类
四、MySQL无限级分类表设计
无限级分类（php+mysql实现）
2018-12-10
预排序遍历树
2018 10 16
PHP无限级分类
laravel无限级分类

本文案例以MySQL5.7作为数据库环境。

开发过程中难免会遇到一些奇葩的却又不得不实现的需求。

比如在我们的业务系统中用户需要自己设置分类，并为商品设置分类，且满足以下需求：

分类可以是任意层次级别的
商品可以将层级中的任一级作为分类（如某商品分类为B01，B01分类层次为A01>B01>C01>D01...）
在应用程序中，需要显示商品的完整分类层次（如分类为B01，需要显示A01>B01，而不是只显示B01）
在应用程序中，能够根据分类的上级分类来查询，不仅仅按此分类查询（比如分类为C01，层次为A01>B01>C01，使用A01、B01、C01均要求能够查到此分类C01）

仔细考虑下种种要求，还是稍微有点复杂的，设计不同最终的性能也会有差异，而且差异还不小。

如下表所示，每个商品的分类层次可以不一致，层次也没有上限。

商品	分类
商品1	A01>B01>C02
商品2	A01
商品3	A02>B02>C01>D03
商品4	A03>B03

此时，这种需求肯定不能用省>市>区（县）这种固定级别的表的数据结构来设计，我们可以采用链表（可以理解为指针）的方式来设计表结构，当然省>市>区（县）的需求也可以用这种链表方案。

# 自定义分类表
mysql> create table t_category(
    -> id bigint(20) primary key,
    -> name varchar(20) comment '分类名称',
    -> upper_id bigint(20) comment '上级分类id'
    -> );
# 准备分类数据
INSERT INTO `t_category` (`id`, `name`) VALUES ('10001', 'A01');
INSERT INTO `t_category` (`id`, `name`) VALUES ('10002', 'A02');
INSERT INTO `t_category` (`id`, `name`) VALUES ('10003', 'A03');
INSERT INTO `t_category` (`id`, `name`) VALUES ('20001', 'B01');
INSERT INTO `t_category` (`id`, `name`) VALUES ('20002', 'B02');
INSERT INTO `t_category` (`id`, `name`) VALUES ('20003', 'B03');
INSERT INTO `t_category` (`id`, `name`) VALUES ('30001', 'C01');
INSERT INTO `t_category` (`id`, `name`) VALUES ('30002', 'C02');
INSERT INTO `t_category` (`id`, `name`) VALUES ('30003', 'C03');
INSERT INTO `t_category` (`id`, `name`) VALUES ('40001', 'D01');
INSERT INTO `t_category` (`id`, `name`) VALUES ('40002', 'D02');
INSERT INTO `t_category` (`id`, `name`) VALUES ('40003', 'D03');
# 创建商品表
mysql> create table t_goods(
    -> id bigint(20) primary key,
    -> name varchar(50),
    -> goods_category varchar(1000)
    -> );
# 准备商品数据（将分类层级保存到商品分类字段）
INSERT INTO `t_goods` (`id`, `name`, `goods_category`) VALUES ('1', '商品1', '10001/20001/30002');
INSERT INTO `t_goods` (`id`, `name`, `goods_category`) VALUES ('2', '商品2', '10001');
INSERT INTO `t_goods` (`id`, `name`, `goods_category`) VALUES ('3', '商品3', '10002/20002/30001/40003');
INSERT INTO `t_goods` (`id`, `name`, `goods_category`) VALUES ('4', '商品4', '10003/20003');

以上方案是我感觉比较好的设计，避免了循环递归查询（尤其要避免应用程序与数据库多次交互）。

最后演示下将商品分类中的id通过sql转成name，其它的没难度不再演示了，这个问题的关键在于数据结构的设计和分类数据的保存。当然，有些工作也可以放在应用程序完成。

# 准备用于辅助的t_sequence
mysql> create table t_sequence(
    -> id bigint primary key auto_increment
    -> );
# 准备1-10（如有需要可更多）
mysql> insert into t_sequence values (),(),(),(),(),(),(),(),(),();
# 利用cross join将分类信息转成多行
mysql> select a.name,s.id as sequence,substring_index(substring_index(a.goods_category,'/',s.id),'/',-1) as category_id from t_sequence as s
    -> cross join (
    -> select `name`,goods_category,(length(goods_category)-length(replace(goods_category,'/','')) + 1) as len from t_goods
    -> ) as a
    -> on s.id <= a.len;
+---------+----------+-------------+
| name    | sequence | category_id |
+---------+----------+-------------+
| 商品1   |        1 | 10001       |
| 商品1   |        2 | 20001       |
| 商品1   |        3 | 30002       |
| 商品2   |        1 | 10001       |
| 商品3   |        1 | 10002       |
| 商品3   |        2 | 20002       |
| 商品3   |        3 | 30001       |
| 商品3   |        4 | 40003       |
| 商品4   |        1 | 10003       |
| 商品4   |        2 | 20003       |
+---------+----------+-------------+
# 进一步完善将分类id转成name，这里又使用了一次join，可以看到离最终结果越来越近了
mysql> select b.name as goods,b.sequence,c.name as category from t_category as c
    -> join (
    -> select a.name,s.id as sequence,substring_index(substring_index(a.goods_category,'/',s.id),'/',-1) as category_id from t_sequence as s
    -> cross join (
    -> select `name`,goods_category,(length(goods_category)-length(replace(goods_category,'/','')) + 1) as len from t_goods
    -> ) as a on s.id <= a.len
    -> ) as b on b.category_id = c.id;
+---------+----------+----------+
| goods   | sequence | category |
+---------+----------+----------+
| 商品1   |        1 | A01      |
| 商品1   |        2 | B01      |
| 商品1   |        3 | C02      |
| 商品2   |        1 | A01      |
| 商品3   |        1 | A02      |
| 商品3   |        2 | B02      |
| 商品3   |        3 | C01      |
| 商品3   |        4 | D03      |
| 商品4   |        1 | A03      |
| 商品4   |        2 | B03      |
+---------+----------+----------+
# 最后刚好MySQL中有group_concat()这个函数可以解决我们的问题，另外我们把上面的商品名称换成id，因为名称是可能重复的（上面的示例只是为了看得明显）
mysql> select d.id,d.`name`,group_concat(category separator '>') as category from
    -> (
    -> select b.id,b.`name`,b.sequence,c.name as category from t_category as c
    -> join (
    -> select a.id,a.name,s.id as sequence,substring_index(substring_index(a.goods_category,'/',s.id),'/',-1) as category_id from t_sequence as s
    -> cross join (
    -> select id,`name`,goods_category,(length(goods_category)-length(replace(goods_category,'/','')) + 1) as len from t_goods
    -> ) as a on s.id <= a.len
    -> ) as b on b.category_id = c.id
    -> ) as d group by id,`name`;
+----+---------+-----------------+
| id | name    | category        |
+----+---------+-----------------+
|  1 | 商品1   | A01>B01>C02     |
|  2 | 商品2   | A01             |
|  3 | 商品3   | A02>B02>C01>D03 |
|  4 | 商品4   | A03>B03         |
+----+---------+-----------------+

大功告成，再理一下思路
Step 1：cross join连接t_sequence与t_goods将分类层级转成多行，并用substring_index()函数拆分各层级分类
Step 2：inner join连接第一步的结果与t_category
Step 3：利用group_concat函数将多行转一行