如果db的版本是8.0+，那么 with recursive cte as 子句可以在以下select语句中使用（在提供所需的dml之后，如create table和insert语句）：

mysql> create table tab( ID int, suggestions varchar(25));
mysql> insert into tab values(1,'A,B,C');
mysql> insert into tab values(2,'D,E,F,G,H');
mysql> select q2.*,
              row_number() 
              over 
             (partition by q2.id order by q2.suggestion) as number 
  from 
(       
select distinct
       id, 
       substring_index(
        substring_index(suggestions, ',', q1.nr), 
        ',', 
        -1
       ) as suggestion
    from tab
    cross join
    (with recursive cte as
     (
      select 1 as nr
      union all
      select 1+nr from cte where nr<10
      )
     select * from cte) q1
) q2;

+------+------------+--------+
| id   | suggestion | number |
+------+------------+--------+
|    1 | A          |      1 |
|    1 | B          |      2 |
|    1 | C          |      3 |
|    2 | D          |      1 |
|    2 | E          |      2 |
|    2 | F          |      3 |
|    2 | G          |      4 |
|    2 | H          |      5 |
+------+------------+--------+

我建议使用一系列子查询：

select id, substring_index(suggestions, ',', 1) as suggestion, 1
from example
where suggestions is not null
union all
select id, substring_index(substring_index(suggestions, ',', 2), ',', -1) as suggestion, 2
from example
where suggestions like '%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 3), ',', -1) as suggestion, 3
from example
where suggestions like '%,%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 4), ',', -1) as suggestion, 4
from example
where suggestions like '%,%,%,%'
union all
select id, substring_index(substring_index(suggestions, ',', 5), ',', -1) as suggestion, 5
from example
where suggestions like '%,%,%,%,%';

如果每个id有5个以上的选项，则可以很容易地进行扩展。

在这里找到同样的问题解决了。

https://gist.github.com/avoidwork/3749973