mysql分割字符串 转行

-- 创建示例表
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255)
);

-- 插入示例数据
INSERT INTO example (data) VALUES ('apple,banana,cherry');

-- 使用SUBSTRING_INDEX函数分割字符串
SELECT 
    SUBSTRING_INDEX(data, ',', 1) AS field1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(data, ',', 2), ',', -1) AS field2,
    SUBSTRING_INDEX(data, ',', -1) AS field3
FROM example;

-- 使用REGEXP_SUBSTR函数分割字符串
SELECT 
    REGEXP_SUBSTR(data, '[^,]+', 1, 1) AS field1,
    REGEXP_SUBSTR(data, '[^,]+', 1, 2) AS field2,
    REGEXP_SUBSTR(data, '[^,]+', 1, 3) AS field3
FROM example;

-- 创建自定义函数来分割字符串
DELIMITER $$
CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE output VARCHAR(255);
    SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
        LENGTH(SUBSTRING_INDEX(str, delim, pos - 1)) + 1),
        delim, '');
    RETURN output;
END$$
DELIMITER ;

-- 使用自定义函数分割字符串
SELECT 
    SPLIT_STRING(data, ',', 1) AS field1,
    SPLIT_STRING(data, ',', 2) AS field2,
    SPLIT_STRING(data, ',', 3) AS field3
FROM example;