mysql怎么切割字符

SUBSTRING_INDEX(str, delim, count)

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    hobbies VARCHAR(255)
);

INSERT INTO users (name, hobbies) VALUES ('Alice', 'Reading,Swimming,Cooking');
INSERT INTO users (name, hobbies) VALUES ('Bob', 'Photography,Dancing');

SELECT name, SUBSTRING_INDEX(hobbies, ',', 1) AS first_hobby FROM users;

REGEXP_SUBSTR(str, regexp, pos, occurrence)

SELECT name, REGEXP_SUBSTR(hobbies, '[^,]+', 1, level) AS hobby
FROM users
JOIN (
    SELECT 1 AS level UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) levels
WHERE level <= LENGTH(hobbies) - LENGTH(REPLACE(hobbies, ',', '')) + 1;

SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(hobbies, ',', numbers.n), ',', -1)) AS hobby
FROM users
JOIN (
    SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) numbers
WHERE numbers.n <= LENGTH(hobbies) - LENGTH(REPLACE(hobbies, ',', '')) + 1;

SELECT REGEXP_SUBSTR(hobbies, '(\\|[^\\|]+)', 1, level) AS hobby
FROM users
JOIN (
    SELECT 1 AS level UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) levels
WHERE level <= LENGTH(hobbies) - LENGTH(REPLACE(hobbies, '|', '')) + 1;