本文案例以MySQL5.7作為數(shù)據(jù)庫環(huán)境。
開發(fā)過程中難免會遇到一些奇葩的卻又不得不實現(xiàn)的需求刚操。
比如在我們的業(yè)務(wù)系統(tǒng)中用戶需要自己設(shè)置分類勇哗，并為商品設(shè)置分類，且滿足以下需求：
- 分類可以是任意層次級別的
- 商品可以將層級中的任一級作為分類（如某商品分類為B01月褥，B01分類層次為A01>B01>C01>D01...）
- 在應(yīng)用程序中，需要顯示商品的完整分類層次（如分類為B01瓢喉，需要顯示A01>B01宁赤，而不是只顯示B01）
- 在應(yīng)用程序中，能夠根據(jù)分類的上級分類來查詢栓票，不僅僅按此分類查詢（比如分類為C01决左，層次為A01>B01>C01，使用A01走贪、B01佛猛、C01均要求能夠查到此分類C01）
仔細考慮下種種要求，還是稍微有點復(fù)雜的坠狡，設(shè)計不同最終的性能也會有差異挚躯，而且差異還不小。
如下表所示擦秽，每個商品的分類層次可以不一致码荔，層次也沒有上限漩勤。
| 商品 | 分類 |
|---|---|
| 商品1 | A01>B01>C02 |
| 商品2 | A01 |
| 商品3 | A02>B02>C01>D03 |
| 商品4 | A03>B03 |
此時，這種需求肯定不能用省>市>區(qū)（縣）這種固定級別的表的數(shù)據(jù)結(jié)構(gòu)來設(shè)計缩搅，我們可以采用鏈表（可以理解為指針）的方式來設(shè)計表結(jié)構(gòu)越败，當(dāng)然省>市>區(qū)（縣）的需求也可以用這種鏈表方案。
# 自定義分類表
mysql> create table t_category(
-> id bigint(20) primary key,
-> name varchar(20) comment '分類名稱',
-> upper_id bigint(20) comment '上級分類id'
-> );
# 準(zhǔn)備分類數(shù)據(jù)
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');
# 創(chuàng)建商品表
mysql> create table t_goods(
-> id bigint(20) primary key,
-> name varchar(50),
-> goods_category varchar(1000)
-> );
# 準(zhǔn)備商品數(shù)據(jù)（將分類層級保存到商品分類字段）
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');
以上方案是我感覺比較好的設(shè)計硼瓣，避免了循環(huán)遞歸查詢（尤其要避免應(yīng)用程序與數(shù)據(jù)庫多次交互）究飞。
最后演示下將商品分類中的id通過sql轉(zhuǎn)成name，其它的沒難度不再演示了堂鲤，這個問題的關(guān)鍵在于數(shù)據(jù)結(jié)構(gòu)的設(shè)計和分類數(shù)據(jù)的保存亿傅。當(dāng)然，有些工作也可以放在應(yīng)用程序完成瘟栖。
# 準(zhǔn)備用于輔助的t_sequence
mysql> create table t_sequence(
-> id bigint primary key auto_increment
-> );
# 準(zhǔn)備1-10（如有需要可更多）
mysql> insert into t_sequence values (),(),(),(),(),(),(),(),(),();
# 利用cross join將分類信息轉(zhuǎn)成多行
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轉(zhuǎn)成name葵擎，這里又使用了一次join，可以看到離最終結(jié)果越來越近了
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中有g(shù)roup_concat()這個函數(shù)可以解決我們的問題半哟，另外我們把上面的商品名稱換成id酬滤，因為名稱是可能重復(fù)的（上面的示例只是為了看得明顯）
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將分類層級轉(zhuǎn)成多行寓涨，并用substring_index()函數(shù)拆分各層級分類
Step 2：inner join連接第一步的結(jié)果與t_category
Step 3：利用group_concat函數(shù)將多行轉(zhuǎn)一行
