MySQL把带有分隔符的数据迁移到另一个表

发表于 分类于 ， 阅读次数： 本文字数： 3.3k 阅读时长 ≈ 3 分钟

需求

需要把t_achievement表的所属公司id（company_id）、所属公司名称（company_name）、insert_time字段迁移到t_achievement_company_map表，其中company_id和company_name都是通过"、"拼接。这个时候就需要根据分隔符将一行数据拆分成多行数据。t_achievement_company_map表结构如下图所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE `t_achievement_company_map` (
	`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '主键id',
	`type` VARCHAR(1) NOT NULL DEFAULT '' COMMENT '所属类别   1研发单位  2销售单位  3所属公司' COLLATE 'utf8_general_ci',
	`achievement_id` INT(10) NOT NULL COMMENT '成果库id',
	`company_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 't_company表id' COLLATE 'utf8_general_ci',
	`company_name` VARCHAR(255) NOT NULL COMMENT 't_company表name' COLLATE 'utf8_general_ci',
	`insert_time` BIGINT(19) NOT NULL COMMENT '录入时间',
	PRIMARY KEY (`id`) USING BTREE
)
COMMENT='集团级成果库与公司关联表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

关键函数

substring_index(str,delim,count)

  • str是要处理的字符串
  • delim是分隔符
  • count如果是正数从左往右计数，如果是负数从右往左计数
    举几个例子：
1
2
3
4
5
6
7
8
-- 返回550000
SELECT SUBSTRING_INDEX('550000、680000、470000、120000','、',1)
-- 返回550000、680000、470000
SELECT SUBSTRING_INDEX('550000、680000、470000、120000','、',3)
-- 返回120000
SELECT SUBSTRING_INDEX('550000、680000、470000、120000','、',-1)
-- 返回680000、470000、120000
SELECT SUBSTRING_INDEX('550000、680000、470000、120000','、',-3)

迁移步骤（方法一）

先查询出cmpany_id字段长度最大的记录

1
2
3
SELECT MAX(length(company_id)) FROM t_achievement;
-- 返回company_id为"110000、120000、130000、150000、170100、490200"
SELECT achievement_id,company_id,company_name FROM t_achievement WHERE length(company_id)=51;

往auto_increment表插入数据

auto_increment表的建表sql如下：

1
2
3
4
5
6
CREATE TABLE `auto_increment` (
	`id` INT(10) NULL DEFAULT NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

上面查询出的最大company_id为"110000、120000、130000、150000、170100、490200"。所以至少需要在auto_increment表中插入6条记录。

开始迁移

如果有些company_id不足6个就会重复，加入DISTINCT去重就行。具体代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
INSERT INTO t_achievement_company_map(TYPE,achievement_id,company_id,company_name,insert_time)
SELECT
    DISTINCT '3', 
	 a.achievement_id AS achievement_id,
    substring_index(
        substring_index(
            a.company_id,
            '、',
            b.id+ 1
        ),
        '、' ,- 1
    ) AS company_id,
    substring_index(
        substring_index(
            a.company_name,
            '、',
            b.id+ 1
        ),
        '、' ,- 1
    ) AS company_name,
    a.insert_time AS insert_time
FROM
    t_achievement a
JOIN auto_increment b ON b.id < (
    length(a.company_id) - length(
        REPLACE (a.company_id, '、', '')
    ) + 1
)
WHERE company_id IS NOT NULL AND company_name IS NOT NULL

迁移步骤（方法二）

利用MySQL的help_topic中的help_topic_id，即把auto_increment 替换为mysql.help_topic、id替换为help_topic_id。具体代码如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
INSERT INTO test.t_achievement_company_map(TYPE,achievement_id,company_id,company_name,insert_time)
SELECT
    DISTINCT '3', 
	 a.achievement_id AS achievement_id,
    substring_index(
        substring_index(
            a.company_id,
            '、',
            b.help_topic_id+ 1
        ),
        '、' ,- 1
    ) AS company_id,
    substring_index(
        substring_index(
            a.company_name,
            '、',
            b.help_topic_id+ 1
        ),
        '、' ,- 1
    ) AS company_name,
    a.insert_time AS insert_time
FROM
    test.t_achievement a
JOIN mysql.help_topic b ON b.help_topic_id  < (
    length(a.company_id) - length(
        REPLACE (a.company_id, '、', '')
    ) + 1
)
WHERE company_id IS NOT NULL AND company_name IS NOT NULL