mysql 列逗号拆开_mysql中单位列存在逗号字符串，需要进行分割处理

最新推荐文章于 2024-07-21 03:42:00 发布

离辰肆

最新推荐文章于 2024-07-21 03:42:00 发布

阅读量862

点赞数 1

文章标签： mysql 列逗号拆开

本文链接：https://blog.csdn.net/weixin_29625619/article/details/113165334

版权

本文介绍如何在MySQL中将包含逗号分隔的列数据拆分为多个行，通过创建辅助表和利用SUBSTRING_INDEX函数，实现数据的逐个提取。并讨论了这种方法的优缺点，以及使用GROUP_CONCAT函数进行数据分组连接的方法。

摘要由CSDN通过智能技术生成

今天头一次在mysql中遇到了需要把某一列的数据(是以逗号分隔开的字符串)进行拆分然后分类接着再做分组处理等等，最开始拿到这个完全是懵逼的，这是网上找到的一个亲测有效的方式，可以有效地把需要拆分的列依次罗列下来，这个例子看上去很复杂，其实只需要几个条件：

1.原数据表(就是你需要操作处理的表)

2.建一个放数据的新表(具体方式看例子)

3.本文章最下面的代码，把相应的表名和需要处理的字段修改过来就可以了

4.最后做group by 分类操作，求占比等等就直接在那个新表操作就行了

表数据：

Value

tiny,small,big

small,medium

tiny,big

期望得到结果：

Value

tiny

small

big

small

medium

tiny

big

正文：

#需要处理的表create table tbl_name (ID int ,mSize varchar(100));insert into tbl_name values (1,'tiny,small,big');insert into tbl_name values (2,'small,medium');insert into tbl_name values (3,'tiny,big');

#用于循环的自增表create table incre_table (AutoIncreID int);insert into incre_table values (1);insert into incre_table values (2);insert into incre_table values (3);

select a.ID,substring_index(substring_index(a.mSize,',',b.AutoIncreID),',',-1)fromtbl_name ajoinincre_table bon b.AutoIncreID <= (length(a.mSize) - length(replace(a.mSize,',',''))+1)order by a.ID;

原理分析：

这个join最基本原理是笛卡尔积。通过这个方式来实现循环。

以下是具体问题分析：

length(a.Size) - length(replace(a.mSize,',',''))+1

表示了，按照逗号分割后，改列拥有的数值数量，下面简称n

join过程的伪代码：

根据ID进行循环

{

判断：i 是否 <= n

{

获取最靠近第 i

个逗号之前的数据，即 substring_index(substring_index(a.mSize,',',b.ID),',',-1)

i = i +1

}

ID = ID +1

}

总结：

这种方法的缺点在于，我们需要一个拥有连续数列的独立表(这里是incre_table)。并且连续数列的最大值一定要大于符合分割的值的个数。

例如有一行的mSize 有100个逗号分割的值，那么我们的incre_table 就需要有至少100个连续行。

当然，mysql内部也有现成的连续数列表可用。如mysql.help_topic： help_topic_id

共有504个数值，一般能满足于大部分需求了。

改写后如下:

select a.ID,substring_index(substring_index(a.mSize,',',b.help_topic_id+1),',',-1)fromtbl_name ajoinmysql.help_topic bon b.help_topic_id < (length(a.mSize) - length(replace(a.mSize,',',''))+1)order by a.ID;

改良后：

select

substring_index(substring_index(a.f15,',',b.help_topic_id+1),',',-1) as num ,count(*) as count

from

hw_userinfo_20160509 a

join

mysql.help_topic b

on b.help_topic_id < (length(a.f15) -

length(replace(a.f15,',',''))+1)

GROUP BY num

order by count desc;

反推方式