MySQL 逗号分隔值转多行解决方案

在MySQL中，将逗号分隔的字符串转换为多行记录是一个常见需求。以下是几种有效的方法：

方法一：使用存储过程

DELIMITER //
CREATE PROCEDURE split_string_to_rows(
    IN input_string TEXT,
    IN delimiter_char CHAR(1)
)
BEGIN
    DECLARE str_length INT;
    DECLARE i INT DEFAULT 1;
    DECLARE pos INT DEFAULT 1;
    DECLARE item VARCHAR(255);

    DROP TEMPORARY TABLE IF EXISTS temp_result;
    CREATE TEMPORARY TABLE temp_result (value VARCHAR(255));

    SET str_length = LENGTH(input_string);

    WHILE i <= str_length DO
        IF SUBSTRING(input_string, i, 1) = delimiter_char OR i = str_length THEN
            IF i = str_length THEN
                SET item = SUBSTRING(input_string, pos, i - pos + 1);
            ELSE
                SET item = SUBSTRING(input_string, pos, i - pos);
            END IF;
            INSERT INTO temp_result VALUES (TRIM(item));
            SET pos = i + 1;
        END IF;
        SET i = i + 1;
    END WHILE;

    SELECT * FROM temp_result;
    DROP TEMPORARY TABLE temp_result;
END //
DELIMITER ;

-- 使用示例
CALL split_string_to_rows('apple,banana,orange', ',');

方法二：使用内置函数（MySQL 8.0+）

MySQL 8.0及以上版本可以使用JSON函数：

SELECT 
    TRIM(BOTH '"' FROM JSON_UNQUOTE(JSON_EXTRACT(
        CONCAT('["', REPLACE('apple,banana,orange', ',', '","'), '"]'),
        CONCAT('$[', seq, ']')
    ))) AS value
FROM 
    (SELECT 0 AS seq UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
     SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) AS numbers
WHERE 
    seq < JSON_LENGTH(CONCAT('["', REPLACE('apple,banana,orange', ',', '","'), '"]'));

方法三：使用辅助数字表

-- 首先创建一个辅助数字表（只需创建一次）
CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- 然后使用以下查询
SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', n.n+1), ',', -1) AS value
FROM 
    numbers n
WHERE 
    n.n <= LENGTH('apple,banana,orange') - LENGTH(REPLACE('apple,banana,orange', ',', ''))
ORDER BY 
    n.n;

方法四：使用递归CTE（MySQL 8.0+）

WITH RECURSIVE cte AS (
    SELECT 
        'apple,banana,orange' AS str,
        1 AS pos,
        LOCATE(',', 'apple,banana,orange') AS comma_pos

    UNION ALL

    SELECT 
        str,
        comma_pos + 1,
        LOCATE(',', str, comma_pos + 1)
    FROM 
        cte
    WHERE 
        comma_pos > 0
)
SELECT 
    IF(
        comma_pos > 0,
        SUBSTRING(str, pos, comma_pos - pos),
        SUBSTRING(str, pos)
    ) AS value
FROM 
    cte
ORDER BY 
    pos;

实际应用示例

假设有一个表products，其中tags字段存储逗号分隔的标签：

-- 创建示例表
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    tags VARCHAR(255)
);

INSERT INTO products VALUES 
(1, 'Laptop', 'electronics,computer,gadget'),
(2, 'T-shirt', 'clothing,casual'),
(3, 'Coffee Mug', 'kitchen,home');

-- 使用数字表方法展开标签
SELECT 
    p.id,
    p.name,
    SUBSTRING_INDEX(SUBSTRING_INDEX(p.tags, ',', n.n+1), ',', -1) AS tag
FROM 
    products p
JOIN 
    numbers n
ON 
    n.n <= LENGTH(p.tags) - LENGTH(REPLACE(p.tags, ',', ''))
ORDER BY 
    p.id, n.n;

选择哪种方法取决于您的MySQL版本和具体需求。对于MySQL 8.0+，递归CTE或JSON函数是最现代和灵活的选择。