Column coma separated value

To extract the list of all x,y,z, stored in the same column: (max 5 values)

SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(category, ',', n), ',', -1)) AS category
FROM mytable
JOIN (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) AS numbers
ON CHAR_LENGTH(category) - CHAR_LENGTH(REPLACE(category, ',', '')) >= n - 1
WHERE category IS NOT NULL;

and to also have the number of occurrences

SELECT category, COUNT(*) AS occurrences
FROM (
    SELECT
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(c.category, ',', n), ',', -1)) AS category
    FROM mytable c
    JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) AS numbers
    ON CHAR_LENGTH(c.category) - CHAR_LENGTH(REPLACE(c.category, ',', '')) >= n - 1
    WHERE c.category IS NOT NULL
) AS extracted
GROUP BY category
ORDER BY occurrences DESC;