mysql字符串转换成表

-- 使用CASE WHEN语句处理缺失字段
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', n), ',', -1) AS field,
    CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', n), ',', -1) IS NULL THEN 'default_value' ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', n), ',', -1) END AS processed_field
FROM 
    (SELECT 'value1,value2,,value4' AS str) AS data,
    (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS numbers
WHERE 
    n <= LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;

-- 使用正则表达式提取字段
SELECT REGEXP_SUBSTR(str, '[^,]+', 1, level) AS field
FROM (
    SELECT 'value1,"value2,with,commas",value3' AS str
) AS data
CONNECT BY REGEXP_SUBSTR(str, '[^,]+', 1, level) IS NOT NULL;

-- 示例：将逗号分隔的字符串转换为表
CREATE TABLE temp_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    field1 VARCHAR(255),
    field2 VARCHAR(255),
    field3 VARCHAR(255)
);

INSERT INTO temp_table (field1, field2, field3)
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', 1), ',', -1) AS field1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', 2), ',', -1) AS field2,
    SUBSTRING_INDEX(SUBSTRING_INDEX(str, ',', 3), ',', -1) AS field3
FROM (
    SELECT 'value1,value2,value3' AS str
) AS data;