MySQL巧用公共表表达式(CTE)解决字符串的拆分

一．前言

通常，在进行前端展示数据的需求中，我们都会遇到需要拆分字符串，或者将多行数据合并为一行的需求，当然，这两个需求可以看作是一对相反的操作。

则，一般情况下，在类似如下场景中，我们都是如下写相应的SQL脚本来实现的。

二．数据展示场景

假设，目前我们存在一张表t_goods，存储类似如下的数据：

select * from t_goods;

+------+------+---------------------+

| id | name | parts |

+------+------+---------------------+

| 1 | 衣服 | 尺码,颜色,品牌,产地 |

| 2 | 口红 | 色号,品牌 |

| 3 | 箱包 | 尺寸,重量,品牌 |

+------+------+---------------------+

现在，在web页面上需要展示全部的信息，但是展示数据的过程中，有个小问题，就是，当parts的数据太长的情况下，未显示全的数据部分需要用【...】来展示，如下图所示：

同时，当鼠标移动到【...】时，需要将所有的数据以列表的形式全部展示出来，如下图所示：

三．拆分解决方案

一般情况下，我们都是借助一个有自增属性的列的表（例如表：t_id ）和函数SUBSTRING_INDEX来解决此类问题。

select g.id

, g.name

, substring_index(substring_index(g.parts,',',t.n),',',-1) as part

from t_goods g join t_id t

on t.n <= length(g.parts)-length(replace(g.parts,',',''))+1

order by id;

返回的数据如下：

+------+------+------+

| id | name | part |

+------+------+------+

| 1 | 衣服 | 尺码 |

| 1 | 衣服 | 颜色 |

| 1 | 衣服 | 品牌 |

| 1 | 衣服 | 产地 |

| 2 | 口红 | 色号 |

| 2 | 口红 | 品牌 |

| 3 | 箱包 | 尺寸 |

| 3 | 箱包 | 重量 |

| 3 | 箱包 | 品牌 |

+------+------+------+

但是此种方法也是有比较明显的缺陷与不足的，如果自增列的值缺失，会造成数据返回的不完整。或许你会说，我专门定义一个表来存储，不允许任何人对表做修改，这样可以了吧？但是，如果是单独的服务器，这样是可以的。如果数据库服务器多了呢？分库分服务器也已经是常规操作。同时，另一个潜在的问题，就是，假设你需要维护的这个表中仅仅存储了最大值为100，但是，随着数据的变化，发现100已经无法满足需求，这时，便需要把所有的服务器上的这张表全部维护一遍。

那么是否有种一劳永逸的办法呢？或许MySQL的CTE真的是一个福音。下面就让我们来看看是如何解决的：

为了消除上面的隐含的几个小问题，我们借助递归CTE来试试：

with recursive my_cte as (

select 1 as n

, g.id

, g.name

, substring_index(substring_index(g.parts,',',1),',',-1) as part

from t_goods g

union all

select n+1

, g.id

, g.name

, substring_index(substring_index(g.parts,',',t.n+1),',',-1) as part

from t_goods g join my_cte t

on g.id = t.id and t.n < length(g.parts)-length(replace(g.parts,',',''))+1

)

select id

, name

, part

from my_cte

order by id;

对于递归CTE的用法和工作原理，请见我的另一篇文章：【MySQL之公共表表达式(CTE)】

对于其两者的性能，我这里未作对比，感兴趣的读者可以自行测试。

四．合并解决方案

最后，这里，稍微提下多行数据转换为一行，并用分隔符分割的解决方法，如下：

存在如下表t_goods_detail，其中存储的数据如下：

select * from t_goods_detail;

+------+------+------+

| id | name | part |

+------+------+------+

| 1 | 衣服 | 尺码 |

| 1 | 衣服 | 颜色 |

| 1 | 衣服 | 品牌 |

| 1 | 衣服 | 产地 |

| 2 | 口红 | 色号 |

| 2 | 口红 | 品牌 |

| 3 | 箱包 | 尺寸 |

| 3 | 箱包 | 重量 |

| 3 | 箱包 | 品牌 |

我们通常借助group_concat来解决，SQL如下：

select id

, name

, group_concat(part separator ',')

from t_goods_detail

group by id,name;

上述SQL脚本中，使用逗号(,)作为分隔符，大家也可以自定义分隔符为其它有效的特殊字符。

文末，本文如有不足，不妥之处，也恳请大家批评指出。