Mysql一列拆分多列例子

前言

最近在做一个内部系统的报表统计功能,遇到了一个麻烦的查询场景,因为对sql语句确实不太熟练,在网上查了一些资料,最终找到了一个解法。具体场景和表结构并不复杂,对sql大佬们来说应该也是小case,不过细想确实有趣,特此记录下来。

问题

已知表A有列:主键id、日期date和标签tag,其中tag是逗号分割的多值字符串(值的集合size不超过25个),如下如示例。请统计某段时间(例如20220101-20220131)内,每个tag的记录数。

iddatetag
1202201011
2202201111,2
3202201211,2,3
4202201314
5202201015,8

分析

思路上是把tag中的数据拆分开,然后去统计。比如把上面的行能拆分成如下的多行,然后再进行统计。

iddatetag
1202201011
2202201111
2202201112
3202201211
3202201212
3202201213
4202201314
5202201015
5202201018

解法

网上通常解法

作为不怎么直接写mySql、语法都要查字典的同学,当然先去网上扒一下大家有没有遇到相关问题,是怎么解决的。经过多次搜索,找到了一个相关问题——“MySql一行拆多行”,大家可以自行搜索。
适配场景后的sql如下

SELECT
  t.id,
  t.date,
  substring_index(
    substring_index(
      t.tag,
      ',',
    b.help_topic_id + 1 
  ),
  ',',- 1 
) AS tag 
FROM
  test t
  JOIN 
     mysql.help_topic b 
  ON 
     b.help_topic_id < 
     ( length( t.tag ) - length( REPLACE ( t.tag, ',', '' ) ) + 1 )
分析

直接改造后,在idb上试跑了一下,并没有生成预期的拆分表,翻阅了其他资料,基本都是这一解法,那么先理解这一段sql的含义,再看哪里不work
首先看下里面几个函数和一个特殊的表

  • substring_index:按分隔符截取字符串,substring_index(“待截取有用部分的字符串”,“截取数据依据的字符”,截取字符的位置N);N>0,从前向后截取,N<0,从后向前截取
  • length:字符串长度
  • REPLACE:替换字符串中的内容
  • mysql.help_topic:是mysql 'HELP'指令使用的四张表之一,help_topic_id是这张表的连续自增主键,从0开始

那么上面sql拆分理解下:

length( t.tag ) - length( REPLACE ( t.tag, ',', '' ) ) + 1 
  • 前半部分是计算了字符串中分隔符的个数,+1是字符串中分割出来的单值的个数。
substring_index(
    substring_index(
      t.tag,
      ',',
    b.help_topic_id + 1 
  ),
  ',',- 1 
) AS tag
  • 这部分是把多值tag按照分割符,进行两次分割,分割出对应的单值。

问题升级

如果mysql.help_topic可以使用的话,其实问题到上面应该就结束了。奈何idb上无法使用mysql.help_topic表(具体原因不详,可能是DBA把这个表权限给关了)。
又走不通,那么我们再来深入理解一下mysql.help_topic在这里究竟是起到了什么作用。
想知道它有什么作用也比较简单,其实改造一下sql就一目了然了:

SELECT
  t.id,
  t.date,
  t.tag,
  b.help_topic_id
FROM
  test t
  JOIN 
     mysql.help_topic b 
  ON 
     b.help_topic_id < 
     ( length( t.tag ) - length( REPLACE ( t.tag, ',', '' ) ) + 1 )

拿到的数据应该如下:

iddatetaghelp_topic_id
12022010110
2202201111,20
2202201111,21
3202201211,2,30
3202201211,2,31
3202201211,2,32
42022013140
5202201015,80
5202201015,81

这里是用了help_topic_id从0开始连续增长的特性,在join时,匹配出和tag中值个数相等的多条记录,同时作为每条记录中取多值tag中的第几个值的索引。
理解到这里,这个解法的整体思路就很清晰了,mysql.help_topic能不能用并不是关键了,找一个其他的能从0或1连续自增的表来担任这个功能就ok,大部分的主键id自增的表都可以,甚至可以自己创建一个表,只要能保证连续的个数大于tag中多值的总个数即可。

WHAT IF

既然走到了这,那么不妨再加一点约束条件,如果没有其他的表可用,或者没有其他的表能确保是连续自增,且个数大于tag多值的总个数,怎么办呢(毕竟其他的表随时可能会改,不知道这段依赖的话,改出来都是坑)?或者,我们不专门创建一个新的表(创建新表的话,不知道具体原因的同学,看到也是满脸疑惑——为啥会有这个表???),要怎么解决这个问题呢?
继续查资料后,发现可以使用mySql的自定义变量,生成一组连续的数字代替(MySQL生成连续数字)。下面的sql会生成从1-50的连续数字

SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) xc2,  
            (SELECT @xi:=0) xc0 

最后我的sql变成了这样

SELECT `tag`,COUNT(*)
from(
        SELECT info.`id`, substring_index(substring_index(info.`tag`, ",", b.id), ",", -1)  as tag
            FROM(
                SELECT * from test
                where
                      `tag` IS NOT NULL
                        and `date` >= #{startDate}
                        and `date` <= #{endDate}
                )  as t
            join(
            SELECT @xi:= @xi+ 1 as id
                from
                    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)  xc1,
                    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)  xc2,
                    (SELECT @xi:= 0)  xc0
                ) b
        on b.id <= (length(t.`tag`)  - length( replace(t.`tag`, ",", '')) + 1)
        ) result
        GROUP BY `tag`;

最终的输出应该是:

| tag | count |
| :----:| :----: |
| 1 | 3 |
| 2 | 2 |
| 3 |1 |
| 4 | 1 |
| 5 | 1 |
| 8 |1 |

写在最后

标题起得标题党了,比较少遇到这样的case,解决这个问题过程学到了不少骚操作,感觉挺有意思的。现在的解法并没有考虑性能问题,有更棒的sql欢迎分享。
另外,表设计很重要,不然后面就会遇到这样的奇葩问题,改表结构的话又是非常难受的工作量。

  • 2
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值