mysql split string to multiple row

CREATE TABLE tablename (
id INT,
name VARCHAR(20));

INSERT INTO tablename VALUES
(1, 'a,b,c'),
(2, 'd');

CREATE TABLE numbers (
n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6);

If you can create a numbers table, that contains numbers from 1 to the maximum fields to split, you could use a solution like this:

SELECT
tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
FROM
numbers INNER JOIN tablename
ON CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
id, n


If you cannot create a table, then a solution can be this:
select tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name from (select 1 n union all select 2 union all select 3 union all select 4 union all select 5) numbers INNER JOIN tablename on CHAR_LENGTH(tablename.name) -CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1 order by id, n

solution2 can be use the "WITH" clause :

WITH RECURSIVE numbers AS
(
    SELECT 1 AS n
    UNION
    SELECT n+1 AS n FROM numbers WHERE n<10
)
SELECT
tablename.id, SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
FROM
numbers INNER JOIN tablename
ON CHAR_LENGTH(tablename.name)
-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))>=numbers.n-1
ORDER BY
id, n