校招面试题有了！——记录一个有趣的mySql查询

前言

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

问题

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

分析

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

解法

网上通常解法

作为不怎么直接写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 )

拿到的数据应该如下：

这里是用了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欢迎分享。
另外，表设计很重要，不然后面就会遇到这样的奇葩问题，改表结构的话又是非常难受的工作量。