背景
这是一个微服务架构的项目，其中微服务层分为业务域和基础域两部分。
基础域只提供像文件存取这样的基础服务。其中文件微服务目前支持图片、音频、视频三种类型的文件。三种类型的文件分别存在同一个库不同的表中，相关接口通过文件类型去判断去哪个表中存取数据。
在进行文件上传时，前端直接调用文件服务提供的接口完成上传动作，之后在提交相关表单的时候把该文件的基本信息，如文件名、文件类型、文件在文件服务数据库中的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;
具体使用的知识点放在另一篇文章中整理。
补充
- 有一个坑，就是直接用Navicat去调这个存储过程时，只处理了第一条数据，但是如果是用命名行去处理，就不会有问题。
