mysql 字符串转表

基础概念

MySQL字符串转表通常指的是将一个长字符串按照某种规则拆分成多行数据，并插入到数据库表中的过程。这在处理CSV格式数据、日志文件解析等场景中非常常见。

类型

固定分隔符拆分：使用特定的字符（如逗号、制表符）作为分隔符，将字符串拆分成多个部分。
正则表达式拆分：使用正则表达式来匹配和拆分字符串。
JSON解析：如果字符串是JSON格式，可以直接解析为JSON对象，并插入到相应的表中。

应用场景

日志处理：将日志文件中的长字符串解析为结构化数据，便于后续分析和查询。
数据导入：将CSV或其他格式的文本文件转换为数据库表中的记录。
数据清洗：对不规范的数据进行清洗和格式化，使其符合数据库表的规范。

示例代码

假设我们有一个长字符串，表示多条用户信息，每条信息之间用逗号分隔，每条信息内部用竖线分隔。我们需要将其拆分成单独的用户记录，并插入到users表中。

-- 创建示例表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    age INT
);

-- 示例长字符串
SET @data = 'Alice|alice@example.com|30,Bob|bob@example.com|25,Charlie|charlie@example.com|35';

-- 拆分字符串并插入到表中
INSERT INTO users (name, email, age)
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.value, '|', i), '|', -1) AS name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.value, '|', i+1), '|', -1) AS email,
    SUBSTRING_INDEX(SUBSTRING_INDEX(t.value, '|', i+2), '|', -1) AS age
FROM 
    (SELECT @rownum:=@rownum+1 AS rownum, value FROM 
        (SELECT @rownum:=0) r, 
        (SELECT * FROM (SELECT @data AS data) d, LATERAL (SELECT * FROM JSON_TABLE(data, '$[*]' COLUMNS(value VARCHAR(255) PATH '$')))) t
    ) t
JOIN 
    (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL SELECT 3) idx
ON idx.i <= LENGTH(t.value) - LENGTH(REPLACE(t.value, ',', '')) + 1;