[mysql] 一行变多行

1. 数据表

CREATE TABLE `table_main` (
  `ID` char(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `zb_list_str` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci ,
  `kf_list_str` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  PRIMARY KEY (`ID`) USING BTREE,

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ;

2. 一行变多行

SELECT
*
FROM
  table_main,
  JSON_TABLE (
    JSON_EXTRACT( CONCAT( '["', REPLACE ( zb_list_str, ',', '","' ), '"]' ), '$' ),
  '$[*]' COLUMNS (id FOR ORDINALITY,  `zb` VARCHAR (255) PATH '$' )) AS t_zb JOIN 
  JSON_TABLE (
    JSON_EXTRACT( CONCAT( '["', REPLACE ( kf_list_str, ',', '","' ), '"]' ), '$' ),
  '$[*]' COLUMNS (id FOR ORDINALITY,`kf` VARCHAR (255) PATH '$' )) AS t_kf 

3. 步骤解释说明

1. 用CONCAT() 和REPLACE () 将待拆分字符串str str_part1,str_part2,str_part3 拼成json数组格式的字符串json_array_str “[“str_part1”,“str_part2”,“str_part3”]”
2. 用JSON_EXTRACT() 将json_array_str 转成 json_array
3. 用JSON_TABLE () (对mysql 版本有要求) 将json_array渲染成一个表格(可以有多行)即可

4. 其他

• 可以同时如此操作多个字段
• 每个字段拆分出来的表格(如t_zb)可像普通表格一样进行join等操作(示例中需要两张表的笛卡尔积)