MySql行转列实战（根据符号分割并分行展示）

Moshow郑锴 2020-11-15 12:10:55

244

分类专栏：数据库文章标签： mysql分割字符串 mysql分割并分行 mysql根据字符串分割 mysql根据逗号分号分行

本文链接：https://blog.csdn.net/moshowgame/article/details/109702110

版权

前言

最近有个爬虫系统，需要针对抓取下来的信息中的单位进行处理，而单位是通过分号连接的字符串，例如xxx公司;xxx公司。在mysql中根据符号;进行分割，并分行展示，也就是行转列。

解决方案

其中title是信息标题，company是公司名称。(基于真实项目实战，字段做了处理)

select t.title,t.company from xxx t

SELECT
    a.title,SUBSTRING_INDEX( SUBSTRING_INDEX( a.company, ';', b.help_topic_id + 1 ), ';',- 1 ) 
FROM
    unit a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.company) - LENGTH( REPLACE ( a.company, ';', '' ) ) + 1 );

mysql.help_topic

help_topic表是数据库mysql下的一个表，用于保存帮助文档。这里只是借助其help_topic_id进行处理而已，可以看SUBSTRING_INDEX( SUBSTRING_INDEX( a.company, ';', b.help_topic_id + 1 ), ';',- 1 ) 这句。

MariaDB官方的文档：https://mariadb.com/kb/en/mysqlhelp_topic-table/
在这里插入图片描述

SUBSTRING_INDEX

相信这里才是精华所在： SUBSTRING_INDEX(s, delimiter, number) ，返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。

如果 number 是正数，返回第 number 个字符左边的字符串。
SELECT SUBSTRING_INDEX('a*b*c*d*e','*',3); ---- a*b*c
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
SELECT SUBSTRING_INDEX('a*b*c','*',-1); ---- c
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1); ---- c

在这里插入图片描述

select a.id, a.username, 
substring_index(substring_index(a.course, '|', b.help_topic_id + 1), '|', -1) course  
from student a JOIN mysql.help_topic b 
ON b.help_topic_id < (length(a.course) - length(REPLACE(a.course, '|', '')) + 1);

Production行转列实战

假设：有Unit表，根据bid_unit字段进行行转列 并存入unit_detail 详情表。
前提：表引擎InnoDB+行转列字段已设置 FULLTEXT`全文索引
时间：17w条记录 ~ 25秒

SQL：（部分敏感字段已做处理），请根据需要选择字段+设置条件，总体内容以及格式不变

insert into unit_detail_test(
SELECT
    a.unit_id,a.source,a.owner_unit,SUBSTRING_INDEX( SUBSTRING_INDEX( a.bid_unit, ';', b.help_topic_id + 1 ), ';',- 1 )  as 'bid_unit',a.price,a.province,a.city,a.status
FROM
    unit a
    JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.bid_unit) - LENGTH( REPLACE ( a.bid_unit, ';', '' ) ) + 1 )
  where a.status=2 and a.fix_flag=0
  
  );