记一次需求变更导致的数据库数据迁移

P80814-184047.jpg

背景

这是一个微服务架构的项目，其中微服务层分为业务域和基础域两部分。

基础域只提供像文件存取这样的基础服务。其中文件微服务目前支持图片、音频、视频三种类型的文件。三种类型的文件分别存在同一个库不同的表中，相关接口通过文件类型去判断去哪个表中存取数据。

在进行文件上传时，前端直接调用文件服务提供的接口完成上传动作，之后在提交相关表单的时候把该文件的基本信息，如文件名、文件类型、文件在文件服务数据库中的id（以下简称为x_file_id）等，发给后端去处理。在进行文件下载时，后端把这些基本信息取出来发给前端，在用户执行下载操作时，前端再调用文件服务提供的接口完成下载动作。

虽然提供了不同的微服务去操作数据库，但该项目并没有分库分表，所有服务都基于同一MySQL数据库进行。也就是说，跨库的查询操作也可以用一个大SQL去完成。

有这样一个模块，设计之初仅支持上传图片附件。原设计者的实现策略是把前端发来的x_file_id拼接成字符串存入数据库相应的表中（最多支持5个附件），然后跟前端约定，这个字段返回的是图片附件。前端在调用文件服务的接口时，先对后端传来的字符串进行分割，然后按照约定指定要取的文件类型为图片，从而完成与文件服务数据库的对应。

---

需求

之前需要存图片附件的业务表（tb_business），简化后：

id	pictures
ac0a5442a12411e88cd268f728b4be42	be591621a12411e88cd268f728b4be42,c94b88a7a12411e88cd268f728b4be42
b647f6efa12411e88cd268f728b4be42	d13b891ca12411e88cd268f728b4be42

文件服务中，用来存储图片的表（tb_picture），简化后：

id	file_name	suffix
be591621a12411e88cd268f728b4be42	apple	.jpg
c94b88a7a12411e88cd268f728b4be42	banana	.jpg
d13b891ca12411e88cd268f728b4be42	orange	.jpg

tb_business的pictures字段，存储的是与该条数据相关的附件在tb_picture中的id

需求总是会变的。由于业务需要，现在必须同时支持图片和视频附件，而且对附件的数量没有限制。这时候使用中间表的形式去专门存各种类型的附件就更合理。

中间表（tb_r_business_file）：

id	business_id	file_id	file_name	file_type
a8f930cea12811e88cd268f728b4be42	ac0a5442a12411e88cd268f728b4be42	be591621a12411e88cd268f728b4be42	apple	.jpg
a9dc75a6a12811e88cd268f728b4be42	ac0a5442a12411e88cd268f728b4be42	c94b88a7a12411e88cd268f728b4be42	banana	.jpg
aa433971a12811e88cd268f728b4be42	b647f6efa12411e88cd268f728b4be42	d13b891ca12411e88cd268f728b4be42	orange	.jpg

id：中间表的主键

business_id：业务表的主键

file_id：文件表的主键

file_name：文件名

file_type：文件类型

要实现这个需求，不光代码需要改，还需要把生产上已有的数据，从tb_business中导入到tb_r_business_file中。

---

实现

由于涉及到字符串分割，分割后还需要遍历，很自然的就想到了存储过程。

-- 调用时需要传两个参数，split是用来分割的字符串，step指分割后单个字符串的长度
DELIMITER //
CREATE DEFINER = `root`@`localhost` PROCEDURE `refreshFileData`(IN split varchar(10), IN step int)
BEGIN
    DECLARE location INT;  -- 分隔符所在位置，默认为1
    DECLARE startIndex INT;  -- 字符串截取的开始位置
    DECLARE originalStr VARCHAR(255);  -- 未处理的原始字符串
    DECLARE subStr VARCHAR(32);  -- 分割后的子字符串
    DECLARE businessId VARCHAR(32);
    DECLARE done INT DEFAULT 0;

    DECLARE cur_business CURSOR FOR SELECT id, pictures FROM tb_business;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN cur_business;

    REPEAT
        FETCH cur_business INTO businessId, originalStr;
        IF done <> 1 AND originalStr IS NOT NULL THEN  -- 如果不加对originalStr的NULL值判断，会造成中间表中出现垃圾数据
            SET originalStr = LTRIM(RTRIM(originalStr));  -- 为了防止字符串两边有空格的情况出现
            SET startIndex = 1;
            SET location = 1;

            WHILE location <> 0 DO
                SET subStr = SUBSTRING(originalStr, startIndex, step);

                INSERT INTO tb_r_business_file VALUES(REPLACE(UUID(), '-', ''), businessId, subStr, NULL, NULL);
                UPDATE tb_r_business_file rf INNER JOIN (SELECT id, suffix, file_name FROM tb_picture) temp ON rf.file_id = temp.id SET rf.file_type = temp.suffix, rf.file_name = temp.file_name;

                SET location = LOCATE(split, originalStr, startIndex);
                SET startIndex = location + 1;
            END WHILE;

        END IF;

    UNTIL done END REPEAT;
    CLOSE cur_business;

END
//

后来发现没有执行存储过程的权限，所以，又使用纯sql的方式实现了一下

-- 为了解决遍历问题，创建辅助表
CREATE TABLE `file_transfer_temp`(
    `id` int(11) NOT NULL
)

INSERT INTO 'file_transfer_temp' VALUES(1);
INSERT INTO 'file_transfer_temp' VALUES(2);
INSERT INTO 'file_transfer_temp' VALUES(3);
INSERT INTO 'file_transfer_temp' VALUES(4);
INSERT INTO 'file_transfer_temp' VALUES(5);

INSERT INTO tb_r_business_file SELECT
    REPLACE(UUID(), '_', '') id,
    refresh.business_id,
    refresh.file_id,
    pic.suffix file_type,
    pic.file_name
FROM
    tb_picture pic,
    (
        SELECT
            temp.business_id,
            -- -1 表示返回字符串最后一个分隔符之后的子字符串，如果没有分隔符，则返回整个字符串
            -- 内外层的SUBSTRING_INDEX共同实现了通过遍历取出子字符串的功能
            SUBSTRING_INDEX(
                -- id相当于遍历时当前为第n次，该语句拿到的是当第n个分隔符之前的所有字符组成的子字符串
                -- 如果n的数值超过字符串中分隔符的数量，返回整个字符串
                SUBSTRING_INDEX(
                    temp.pictures,
                    ',',
                    id
                )
            ',',-1
            ) file_id
        FROM
            -- 辅助表正序排列，作用是实现遍历
            (
                SELECT
                    *
                FROM
                    file_transfer_temp
                ORDER BY
                    id ASC
            ) id_temp,
            -- 新的虚拟表包含业务表id，需要分割的字符串，子字符串的数量
            (
                SELECT
                    -- 原字符串的长度 - 去掉分隔符的字符串的长度 + 1 = 子字符串的数量
                    LENGTH(
                        business_temp.pictures
                    ) - LENGTH(
                        REPLACE(
                            business_temp.pictures,
                            ',',
                            ''
                        )
                    ) + 1 AS strLength,
                    business_temp.id business_id,
                    business_temp.pictures
                FROM
                    -- 原业务表中有很多字段，但要实现该需求只关心这两个
                    (
                        SELECT
                            id,
                            pictures
                        FROM
                            tb_business
                    ) business_temp
            ) temp
        WHERE id_temp.id <= temp.strLength
    ) refresh
WHERE 
    refresh.file_id = pic.id;

具体使用的知识点放在另一篇文章中整理。

---

补充

1. 有一个坑，就是直接用Navicat去调这个存储过程时，只处理了第一条数据，但是如果是用命名行去处理，就不会有问题。