MySQL分割一行为多行的思路

傷城~ 2023-07-25 13:00 21阅读 0赞

最近数据分析有需求，分析运营活动短信用户，但是发送短信的用户是通过 JSON 字符串数组存储在一个 text 字段的。内容类似于：

["user1", "user2", "user3"....]

数据分析想分析这些用户，那么就需要 in 这些用户查询。自己手动拼 SQL 太蛋疼，而且好几万几十万的用户，拼成SQL，复制粘贴也够蛋疼的。那么可以考虑将这一行分割为多行，作为一个字段。

## mysql.help\_topic 是啥 ##

网上的思路是利用 `mysql.help_topic` 这个记录表，这个表是存储 mysql 各种帮助文档目录的，主要因为他有一个**从零开始自增的 id 字段**，所以采用这张表作为帮助表。其实他不是用来干这个的。并且，有时候我们精简安装，或者是云服务里面的 mysql，他们的这张表里面的内容，是空的，所以我们不能靠这张表。

## 如何自己实现呢？ ##

思路主要是如下，首先处理数据，将 JSON 字符串数组处理成：

user1,user2,user3

通过：

select replace(replace(replace(replace(a,'[',''),']',''), '"', ''),' ','') processed_data from 表

然后，我们通过`substring_index`函数，可以提取出`user1`，`user2`，`user3`这些用户 id。分别是：`substring_index(substring_index(processed_data,',',1),',',-1)`，`substring_index(substring_index(processed_data,',',2),',',-1)`，`substring_index(substring_index(processed_data,',',3),',',-1)`.可以看出，如果我们能提供一个数字，这个数字从1开始，一直到`,`的个数 + 1，这样就能使用`substring_index`函数，将每个 userId 提取出来，也就是将数据转换成：

+-------------------+----+
    | processed_data    | id |
    | user1,user2,user3 | 1  |
    | user1,user2,user3 | 2  |
    | user1,user2,user3 | 3  |

哪里有这么一张表呢？我们可以创建一个表，里面只有一列 `id`，从0或者1开始，这里我们从0开始，一直到你的`,`可能的最多个数，我们这里是 200 万。

+----+
    | id |
    +----+
    |  0 |
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |

这样，通过 join 这张表，用 id < `,`的个数为条件，就能得出上面的`processed_data`与`id` join 的数据。

最后的SQL：

SELECT
    	substring_index( substring_index( processed_data, ',', b.id + 1 ), ',',- 1 ) user_id 
    FROM
    (
    SELECT REPLACE
    	(
    		REPLACE ( REPLACE ( REPLACE (数据字段, '[', '' ), ']', '' ), '"', '' ),
    		' ',
    		'' 
    	) processed_data 
    FROM
    	表 
    ) temp
    JOIN help表 b ON b.id < ( length( temp.processed_data ) - length( REPLACE ( temp.processed_data, ',', '' ) ) + 1 )

其中的 help 表就是里面只有一列 `id`，从0或者1开始，这里我们从0开始，一直到你的`,`可能的最多个数的这张表