MySql行转列实战(根据符号分割并分行展示)

前言

最近有个爬虫系统,需要针对抓取下来的信息中的单位进行处理,而单位是通过分号连接的字符串,例如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
  
  );
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 博客之星2020 设计师:CY__ 返回首页