mysql 字符串转表

-- 创建示例表
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;