mysql如何使用SUBSTRING_INDEX函数对对应的字符串实现列转行的效果？

Question

问题描述

mysql如何使用SUBSTRING_INDEX函数对对应的字符串实现列转行的效果？

我在用mysql的SUBSTRING_INDEX函数处理表中的一个字段值时遇到了这个问题，这个字段中的数据是如下这个样子（注意这是一个单元格内的数据长这个样子，不是多个单元格）

使用过【35869209395881】的号码清单：
[20230608,广东,广州,19876298082],[20230609,广东,广州,19864391337],[20230610,广东,广州,19875475010]
使用过【86536704443634】的号码清单：
[20230320,广东,广州,13660250915],[20230412,广东,广州,13527671189],[20230523,广东,广州,13527864794],[20230523,广东,广州,13602403001],[20230523,广东,广州,15975441679],[20230524,广东,广州,13760718971],[20230525,广东,广州,13710479054],[20230606,广东,广州,13710034067],[20230608,广东,广州,13416248767],[20230608,广东,广州,19864070445],[20230608,广东,广州,19876298082]
使用过【86855103086379】的号码清单：
[20230407,广东,广州,17278756850],[20230407,广东,广州,19513761138],[20230407,广东,广州,19576161138],[20230408,广东,广州,13512761189],[20230408,广东,广州,13527671189],[20230524,广东,广州,13527864794],[20230525,广东,广州,13609703269],[20230610,广东,广州,19864070445],[20230610,广东,广州,19876298082],[20230612,广东,广州,15790403652]
使用过【86947206218694】的号码清单：
[20230613,广东,广州,19876298082]

问题出现的环境背景及自己尝试过哪些方法

我的问题是出现在mysql处理这个表phone_number_demo中的IMEI_rel字段过程时遇到的，
当时我采用了SUBSTRING_INDEX函数进行拆分，但是我的处理方法最终只实现到了将[]内的内容拆分出来,【】中的序列号始终没有拆分出来。

相关代码

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 1), '[', -1) AS date,
SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 2), ',', -1) AS region,
SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 3), ',', -1) AS city,
SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', -1), ']', 1) AS phone
FROM
(
    SELECT
        SUBSTRING_INDEX(SUBSTRING_INDEX(IMEI_rel, ']', n), '[', -1) AS data
    FROM
        phone_number_demo,
        (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3) AS numbers
    WHERE
            IMEI_rel LIKE '使用过%'
    ORDER BY
        IMEI_rel, n
   ) AS subquery;

你期待的结果是什么？实际看到的错误信息又是什么？

我的目的是将【】和[]中的数据拆分出来并且一一对应，实现的效果为
序列号 日期 省份 城市 手机号
XXX XXX XXX XXX XXX

最终拆分出来的效果是

根据一楼评论的sql跑出来的数据是如下这个样子的

Answer 1

用MySQL的SUBSTRING_INDEX和SUBSTRING函数来拆分字符串

SET SESSION group_concat_max_len = 1000000;

DROP TEMPORARY TABLE IF EXISTS temp_data;
CREATE TEMPORARY TABLE temp_data AS
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(IMEI_rel, '使用过', -1), '】', numbers1.n), '【', -1) AS serial_number,
    SUBSTRING_INDEX(SUBSTRING_INDEX(IMEI_rel, '】', numbers1.n), '【', -1) AS data_segment
FROM 
    phone_number_demo
JOIN 
    (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS numbers1
ON 
    CHAR_LENGTH(IMEI_rel) - CHAR_LENGTH(REPLACE(IMEI_rel, '【', '')) >= numbers1.n - 1
ORDER BY 
    IMEI_rel, numbers1.n;

DROP TEMPORARY TABLE IF EXISTS temp_result;
CREATE TEMPORARY TABLE temp_result AS
SELECT 
    serial_number,
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 1), '[', -1) AS date,
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 2), ',', -1) AS region,
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', 3), ',', -1) AS city,
    SUBSTRING_INDEX(SUBSTRING_INDEX(data_segment, ',', -1), ']', 1) AS phone
FROM 
    temp_data
JOIN 
    (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) AS numbers2
ON 
    CHAR_LENGTH(data_segment) - CHAR_LENGTH(REPLACE(data_segment, '[', '')) >= numbers2.n - 1
ORDER BY 
    serial_number, numbers2.n;

SELECT 
    SUBSTRING(serial_number, 2, CHAR_LENGTH(serial_number) - 2) AS serial_number,
    date,
    region,
    city,
    phone
FROM 
    temp_result;