MySql 一行变多行(根据特定符号分割)

àì夳堔傛蜴生んèń 2022-09-03 13:18 371阅读 0赞

# 一、测试数据 #

DROP TABLE IF EXISTS `test`;
    CREATE TABLE IF NOT EXISTS `test` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT ,
    `name` varchar(255) DEFAULT NULL,
    `num` int(8),
    PRIMARY KEY (`id`)
    );
    INSERT INTO `test`(`name`, `num`) VALUES ('a1,b258,c', 11);
    INSERT INTO `test`(`name`, `num`) VALUES ('f,g123456,h,i85,j', 33);
    INSERT INTO `test`(`name`, `num`) VALUES ('d,e1234', 22);

那么: `SELECT * FROM test;` :

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

那么，把数据转换成为下面这样，需要怎么样实现呢:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTAyMTA2_size_16_color_FFFFFF_t_70_pic_center]

# 二、普通 sql 实现(需要依赖 mysql.help\_topic 表) #

SELECT
        a.id,a.num,SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ',', b.help_topic_id + 1 ), ',',-1 ) name
    FROM
        test a
        JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`name`) - LENGTH( REPLACE ( a.`name`, ',', '' ) ) + 1 );

# 三、mysql.help\_topic 无权限处理办法 #

`mysql.help_topic` 的作用是对 SUBSTRING\_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。

如果 `mysql.help_topic` 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。

获取该字段最多可以分割成为几个字符串:

SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;

创建临时表，并给临时表添加数据:

注意:

1.  临时表必须有一列从 0 或者 1 开始的自增数据
2.  临时表表名随意，字段可以只有一个
3.  临时表示的数据量必须比 `MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1)` 的值大

DROP TABLE IF EXISTS `tmp_help_topic`;
    CREATE TABLE IF NOT EXISTS `tmp_help_topic` (
      `help_topic_id` bigint(20) NOT NULL AUTO_INCREMENT ,
      PRIMARY KEY (`help_topic_id`)
    );
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();
    INSERT INTO `tmp_help_topic`() VALUES ();

查询:

SELECT
        a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) name
    FROM
        test a
        JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );

# 四、函数的意思 #

### 4.1 REPLACE 函数: ###

把 字符串   a,b,c,d   里面的逗号替换成空字符串
    SELECT REPLACE('a,b,c,d', ',', '');
    -- 输出: abcd

那么:

# 获取逗号的个数
    SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));
    
    # 按照逗号分割后会有几个元素，这里分割后就是 a b c d，就是 4 个元素 
    SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);

### 4.2 SUBSTRING\_INDEX 函数: ###

SUBSTRING\_INDEX 是字符串截取函数

SUBSTRING_INDEX(str, delim, count)

1.  str : 表示需要拆分的字符串
2.  delim : 表示分隔符，通过某字符进行拆分
3.  count : 当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

例如:

SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1); -- 返回: a
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2); -- 返回: a*b 
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3); -- 返回: a*b*c
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4); -- 返回: a*b*c*d
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
    SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d

那么:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 1), '*', -1); -- 返回: a
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 2), '*', -1); -- 返回: b
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 3), '*', -1); -- 返回: c
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 4), '*', -1); -- 返回: d

# 五、一行变多行原理 #

回到 sql:

SELECT
        a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) 
    FROM
        test a
        JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );

1.  `SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 )` 就是获取 tmp\_help\_topic 表的 help\_topic\_id 字段的值作为 `name` 字段的第几个子串
2.  使用了 join 就会把字段 name 分为 `(LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 )` 行，并且每行的字段刚好是 `name` 字段的第 help\_topic\_id 个子串

[1342c7e4017444faafad996b519b63cf.png_pic_center]: /images/20220829/dd958dd093e54e52a4024847f09dcbd2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NTAyMTA2_size_16_color_FFFFFF_t_70_pic_center]: /images/20220829/168559c318394e7785a9d5d7280e142b.png