MySQL逗号分割字符串转行，转列，json化

转行

DROP TABLE IF EXISTS `temp_datalist`;
CREATE TEMPORARY TABLE temp_datalist
select distinct
   a.id,
   substring_index(substring_index(a.desc, ',', b.help_topic_id + 1), ',',-1) cd
from
   table a
   join mysql.help_topic as b
      on b.help_topic_id < (char_length(a.desc) - char_length(replace(a.desc, ',', ''))+ 1)
where
   type = 'A'
   and sub_type = 'B';

转列(此例使用【|】进行分割)

 select 
    a.id,
    a.desc,
    substring_index(substring_index(replace(a.desc,'补充|',''),'|',1),'|',-1) a,
    substring_index(substring_index(replace(a.desc,'补充|',''),'|',2),'|',-1) b,
    substring_index(substring_index(replace(a.desc,'补充|',''),'|',3),'|',-1) c,
    substring_index(substring_index(replace(a.desc,'补充|',''),'|',4),'|',-1) d
from
    table a

JSON化

JSON_ARRAYAGG(JSON_OBJECT('name', ifnull(c.code,''))) jsontxt