MySQL分割一行为多行的思路

  • Z时代
  • 2024-01-10
  • 分类：综合

database

最近数据分析有需求，分析运营活动短信用户，但是发送短信的用户是通过 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开始，一直到你的,可能的最多个数的这张表

以上是 MySQL分割一行为多行的思路 的全部内容， 来源链接： utcz.com/z/533155.html

回到顶部