MySQL分割json串取数据

1、创建临时表

CREATE TEMPORARY TABLE temp(
                                   id BIGINT AUTO_INCREMENT,
                                   list TEXT,
                                   PRIMARY KEY(id)
);

2、插入临时表数据

INSERT INTO temp(list)
SELECT  SUBSTRING(list, 2, CHAR_LENGTH(list) - 2) FROM test1;

3、取临时表插入新表

insert into test2(code)
SELECT
TRIM(BOTH '"' FROM TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(list,',',b.help_topic_id + 1), ',', -1) ) ) AS replace_material_code
FROM temp a JOIN mysql.help_topic b ON b.help_topic_id < (CHAR_LENGTH(list) - CHAR_LENGTH(REPLACE(list,',','')) + 1)
WHERE CHAR_LENGTH(list) > 0;