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

我就是我 2022-12-21 06:12 159阅读 0赞

## 前言 ##

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

## 解决方案 ##

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

> select t.title,t.company from xxx t  
> ![在这里插入图片描述][20201115115501849.png_pic_center]

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 );

## ![在这里插入图片描述][20201115115619107.png_pic_center]   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/][https_mariadb.com_kb_en_mysqlhelp_topic-table]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center 1]

## 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`

![在这里插入图片描述][20201115120003735.png_pic_center]

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);

## ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center 2]   Production行转列实战 ##

*  假设：有Unit表，根据`bid_unit`字段进行`行转列` 并存入`unit_detail` 详情表。
 *  前提：表引擎`InnoDB`\+行转列字段已设置 FULLTEXT\`全文索引
 *  时间：17w条记录 ~ 25秒  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70]

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
      
      );

[20201115115501849.png_pic_center]: /images/20221120/3d1567d483c84065ac21dba25d8a0060.png
[20201115115619107.png_pic_center]: /images/20221120/b4aded8517a1451f86dbff786361620e.png
[https_mariadb.com_kb_en_mysqlhelp_topic-table]: https://mariadb.com/kb/en/mysqlhelp_topic-table/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/efdaa281adad4e9eace4c010a2d3d5c7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/457524d51beb438e8079f63b30234f73.png
[20201115120003735.png_pic_center]: /images/20221120/ec25409ad42d48258a53402886f5f33b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/441d9a23434c438aba2110a5cf0f85f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L21vc2hvd2dhbWU_size_16_color_FFFFFF_t_70]: /images/20221120/0bca130d4a794eb3baf8de94af067f48.png