mysql 查询字段分割

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

SELECT id, 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
ON CHAR_LENGTH(hobbies) - CHAR_LENGTH(REPLACE(hobbies, ',', '')) >= numbers.n - 1;

SELECT id, hobby
FROM users,
     UNNEST(REGEXP_SPLIT_TO_ARRAY(hobbies, ',')) AS hobby;

WITH RECURSIVE split_hobbies AS (
    SELECT id, 1 AS pos, SUBSTRING_INDEX(hobbies, ',', 1) AS hobby, SUBSTRING(hobbies, LENGTH(SUBSTRING_INDEX(hobbies, ',', 1)) + 2) AS remaining
    FROM users
    WHERE hobbies IS NOT NULL AND hobbies != ''
    UNION ALL
    SELECT id, pos + 1, SUBSTRING_INDEX(remaining, ',', 1) AS hobby, SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2) AS remaining
    FROM split_hobbies
    WHERE remaining IS NOT NULL AND remaining != ''
)
SELECT id, hobby
FROM split_hobbies;