mysql的split

SELECT 
    SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS part1,
    SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS part2,
    SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS part3;

SELECT * FROM table WHERE FIND_IN_SET('banana', 'apple,banana,orange');

DELIMITER $$
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
           LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1),
           delim, '');
END$$
DELIMITER ;

-- 使用SUBSTRING_INDEX分割字符串
SELECT 
    SUBSTRING_INDEX('apple,banana,orange', ',', 1) AS part1,
    SUBSTRING_INDEX(SUBSTRING_INDEX('apple,banana,orange', ',', 2), ',', -1) AS part2,
    SUBSTRING_INDEX('apple,banana,orange', ',', -1) AS part3;

-- 使用FIND_IN_SET查找特定值
SELECT * FROM table WHERE FIND_IN_SET('banana', 'apple,banana,orange');

-- 自定义SPLIT_STR函数
DELIMITER $$
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
           LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1),
           delim, '');
END$$
DELIMITER ;

-- 使用自定义函数分割字符串
SELECT SPLIT_STR('apple,banana,orange', ',', 2);