mysql把逗号分隔的数据变成独立数据存入关系表

目前有content表里存了一个字段category_id，是多个数据按照“，”分隔的，但是业务需求变更，这个字段的类型不在是单一的了。

也就是以前的表结构

以前的表结构（content）

id	category_id	type
1	129,123,111	0
2	341,890,125	1

现在想要这样的（预期表）

id	category_id	type
1	129	0
1	123	0
1	111	0
2	341	1
2	890	1
2	125	1

 

本来以为会很难，结果我发现了这篇微博https://www.cnblogs.com/yvanBk/p/8555445.html

真的是直接解决了我的问题，再次感谢此博客博主【手动感谢】

贴一下怎么实现的把，首先要先创建一个新的关系表

CREATE TABLE `n_category_relation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `n_id` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'nid',
 `type` int(3) unsigned DEFAULT NULL COMMENT '类型',
  `category_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '类目id',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='关系表';

大概就像上面的ddl语句一样吧

n_category_relation表里

n_id对应content表的id,category_id对应category_id，type对应type

 

下面关键sql来了,这个是先按照预期表需要的查询出数据来

SELECT id as n_id,
    substring_index( substring_index( a.category_id, ',', b.help_topic_id + 1 ), ',',- 1 ) as category_id, type,create_time,update_time
FROM
    content a
     JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.category_id ) - length( REPLACE ( a.category_id , ',', '' ) ) + 1)
		 where a.type is not null

然后插入数据，插入完整sql

insert into n_category_relation (n_id,category_id,type,create_time,update_time) 
SELECT id as n_id,
    substring_index( substring_index( a.category_id, ',', b.help_topic_id + 1 ), ',',- 1 ) as category_id, type,create_time,update_time
FROM
    content a
     JOIN mysql.help_topic b ON b.help_topic_id < ( length( a.category_id ) - length( REPLACE ( a.category_id , ',', '' ) ) + 1)
		 where a.type is not null

原理类的东西我还没搞懂，大家有知道的评论告诉我吧