P80814-184047.jpg

背景

這是一個(gè)微服務(wù)架構(gòu)的項(xiàng)目，其中微服務(wù)層分為業(yè)務(wù)域和基礎(chǔ)域兩部分。

基礎(chǔ)域只提供像文件存取這樣的基礎(chǔ)服務(wù)。其中文件微服務(wù)目前支持圖片、音頻、視頻三種類型的文件。三種類型的文件分別存在同一個(gè)庫(kù)不同的表中，相關(guān)接口通過(guò)文件類型去判斷去哪個(gè)表中存取數(shù)據(jù)。

在進(jìn)行文件上傳時(shí)，前端直接調(diào)用文件服務(wù)提供的接口完成上傳動(dòng)作，之后在提交相關(guān)表單的時(shí)候把該文件的基本信息，如文件名、文件類型、文件在文件服務(wù)數(shù)據(jù)庫(kù)中的id（以下簡(jiǎn)稱為x_file_id）等，發(fā)給后端去處理。在進(jìn)行文件下載時(shí)，后端把這些基本信息取出來(lái)發(fā)給前端，在用戶執(zhí)行下載操作時(shí)，前端再調(diào)用文件服務(wù)提供的接口完成下載動(dòng)作。

雖然提供了不同的微服務(wù)去操作數(shù)據(jù)庫(kù)，但該項(xiàng)目并沒(méi)有分庫(kù)分表，所有服務(wù)都基于同一MySQL數(shù)據(jù)庫(kù)進(jìn)行。也就是說(shuō)，跨庫(kù)的查詢操作也可以用一個(gè)大SQL去完成。

有這樣一個(gè)模塊，設(shè)計(jì)之初僅支持上傳圖片附件。原設(shè)計(jì)者的實(shí)現(xiàn)策略是把前端發(fā)來(lái)的x_file_id拼接成字符串存入數(shù)據(jù)庫(kù)相應(yīng)的表中（最多支持5個(gè)附件），然后跟前端約定，這個(gè)字段返回的是圖片附件。前端在調(diào)用文件服務(wù)的接口時(shí)，先對(duì)后端傳來(lái)的字符串進(jìn)行分割，然后按照約定指定要取的文件類型為圖片，從而完成與文件服務(wù)數(shù)據(jù)庫(kù)的對(duì)應(yīng)。

需求

之前需要存圖片附件的業(yè)務(wù)表（tb_business），簡(jiǎn)化后：

文件服務(wù)中，用來(lái)存儲(chǔ)圖片的表（tb_picture），簡(jiǎn)化后：

tb_business的pictures字段，存儲(chǔ)的是與該條數(shù)據(jù)相關(guān)的附件在tb_picture中的id

需求總是會(huì)變的。由于業(yè)務(wù)需要，現(xiàn)在必須同時(shí)支持圖片和視頻附件，而且對(duì)附件的數(shù)量沒(méi)有限制。這時(shí)候使用中間表的形式去專門存各種類型的附件就更合理。

中間表（tb_r_business_file）：

id：中間表的主鍵

business_id：業(yè)務(wù)表的主鍵

file_id：文件表的主鍵

file_name：文件名

file_type：文件類型

要實(shí)現(xiàn)這個(gè)需求，不光代碼需要改，還需要把生產(chǎn)上已有的數(shù)據(jù)，從tb_business中導(dǎo)入到tb_r_business_file中。

實(shí)現(xiàn)

由于涉及到字符串分割，分割后還需要遍歷，很自然的就想到了存儲(chǔ)過(guò)程。

-- 調(diào)用時(shí)需要傳兩個(gè)參數(shù)，split是用來(lái)分割的字符串，step指分割后單個(gè)字符串的長(zhǎng)度
DELIMITER //
CREATE DEFINER = `root`@`localhost` PROCEDURE `refreshFileData`(IN split varchar(10), IN step int)
BEGIN
    DECLARE location INT;  -- 分隔符所在位置，默認(rèn)為1
    DECLARE startIndex INT;  -- 字符串截取的開(kāi)始位置
    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  -- 如果不加對(duì)originalStr的NULL值判斷，會(huì)造成中間表中出現(xiàn)垃圾數(shù)據(jù)
            SET originalStr = LTRIM(RTRIM(originalStr));  -- 為了防止字符串兩邊有空格的情況出現(xiàn)
            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
//

后來(lái)發(fā)現(xiàn)沒(méi)有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，所以，又使用純sql的方式實(shí)現(xiàn)了一下

-- 為了解決遍歷問(wèn)題，創(chuàng)建輔助表
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 表示返回字符串最后一個(gè)分隔符之后的子字符串，如果沒(méi)有分隔符，則返回整個(gè)字符串
            -- 內(nèi)外層的SUBSTRING_INDEX共同實(shí)現(xiàn)了通過(guò)遍歷取出子字符串的功能
            SUBSTRING_INDEX(
                -- id相當(dāng)于遍歷時(shí)當(dāng)前為第n次，該語(yǔ)句拿到的是當(dāng)?shù)趎個(gè)分隔符之前的所有字符組成的子字符串
                -- 如果n的數(shù)值超過(guò)字符串中分隔符的數(shù)量，返回整個(gè)字符串
                SUBSTRING_INDEX(
                    temp.pictures,
                    ',',
                    id
                )
            ',',-1
            ) file_id
        FROM
            -- 輔助表正序排列，作用是實(shí)現(xiàn)遍歷
            (
                SELECT
                    *
                FROM
                    file_transfer_temp
                ORDER BY
                    id ASC
            ) id_temp,
            -- 新的虛擬表包含業(yè)務(wù)表id，需要分割的字符串，子字符串的數(shù)量
            (
                SELECT
                    -- 原字符串的長(zhǎng)度 - 去掉分隔符的字符串的長(zhǎng)度 + 1 = 子字符串的數(shù)量
                    LENGTH(
                        business_temp.pictures
                    ) - LENGTH(
                        REPLACE(
                            business_temp.pictures,
                            ',',
                            ''
                        )
                    ) + 1 AS strLength,
                    business_temp.id business_id,
                    business_temp.pictures
                FROM
                    -- 原業(yè)務(wù)表中有很多字段，但要實(shí)現(xiàn)該需求只關(guān)心這兩個(gè)
                    (
                        SELECT
                            id,
                            pictures
                        FROM
                            tb_business
                    ) business_temp
            ) temp
        WHERE id_temp.id <= temp.strLength
    ) refresh
WHERE 
    refresh.file_id = pic.id;

具體使用的知識(shí)點(diǎn)放在另一篇文章中整理。

補(bǔ)充

1. 有一個(gè)坑，就是直接用Navicat去調(diào)這個(gè)存儲(chǔ)過(guò)程時(shí)，只處理了第一條數(shù)據(jù)，但是如果是用命名行去處理，就不會(huì)有問(wèn)題。