MySql表数据行列转换方法

由于很多业务表因为历史原因或者性能原因，都使用了违反第一范式的设计模式，即同一个列中存储了多个属性值。这种模式下，应用常常需要将这个列依据分隔符进行分割，并得到列转行的结果：这里使用substring_index函数进行处理

应用场景：

第一种：

select distinct substring_index(substring_index(t.serviceSiteRegion,',',b.rownum),',',-1)  as siteRegion from tb_servicepackage as t

join (select @rownum:=@rownum+1 as rownum from tb_microcode t, (select @rownum:=0) c limit 50) as b 

on b.rownum<=(length(t.serviceSiteRegion) - length(replace(t.serviceSiteRegion,',',''))+1) 

order by convert(siteRegion using gbk);

t.serviceSiteRegion：是需要转化的数据库字段

select @rownum:=@rownum+1 as rownum from tb_microcode t, (select @rownum:=0) c limit 50：取一张不经常改动且数据量不庞大的表的序列号。

distinct：去掉重复的

第二种：

select distinct substring_index(substring_index(t.serviceSiteRegion,',',b.help_topic_id+1),',',-1) 

from dev.tb_servicepackage t join mysql.help_topic b

on b.help_topic_id < (length(t.serviceSiteRegion) - length(replace(t.serviceSiteRegion,',',''))+1)

help_topic是mysql库下的一张表，属于系统表。

使用help_topic时为了解决行转列的问题，出现上面错误是因为用户没有这张表的权限。

解决方法：

可以执行该SQL：GRANT SELECT ON mysql.help_topic TO 'autochain_uat'@'localhost'（给用户赋权限）;

取消用户权限SQL：REVOKE SELECT ON mysql.help_topic FROM 'autochain_uat'@'localhost';

也可以手动操作：

 

结果：