mysql把字符串转数组

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

INSERT INTO users (id, name, hobbies) VALUES
(1, 'Alice', 'reading,swimming'),
(2, 'Bob', 'gaming,cooking');

SELECT id, name, 
    FIND_IN_SET('reading', hobbies) AS reading,
    FIND_IN_SET('swimming', hobbies) AS swimming,
    FIND_IN_SET('gaming', hobbies) AS gaming,
    FIND_IN_SET('cooking', hobbies) AS cooking
FROM users;

ALTER TABLE users ADD COLUMN hobbies_json JSON;

UPDATE users SET hobbies_json = JSON_ARRAYAGG(JSON_UNQUOTE(JSON_EXTRACT(hobbies, '$')));

SELECT id, name, JSON_EXTRACT(hobbies_json, CONCAT('$[', idx, ']')) AS hobby
FROM users, (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) idx
WHERE idx < JSON_LENGTH(hobbies_json);