V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
zhengjian
V2EX  ›  数据库

询问一个 Mysql 数据库一对多关系的存储方式问题

  •  
  •   zhengjian · 2019-05-03 03:28:37 +08:00 · 3488 次点击
    这是一个创建于 1791 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近在做毕设,遇到一个数据库存储的问题,不知该如何选择,请各位指点一二。

    (楼主是一个前端,后端和数据库很多地方不太懂,如果有的地方说的不对,还请大家批评)

    需求描述

    与发微博这个操作类似,我就以发微博来说。

    一条微博包括内容多张图片

    数据库里,内容 就是一个字段,而微博-图片是一个一对多的关系。

    在发微博上传图片的时候,我实现的流程是:

    1. 用户选择图片,点击上传
    2. 后端接收上传的图片,生成文件名后上传到云存储服务商,服务商返回处理后的信息
    3. 服务商返回的图片信息有:图片的宽高、大小等基本信息,和多个不同版本(原图,缩略图)的图片信息( Url 等)
    4. 后端将这张图片的信息存储在图片信息表里,返回前端图片的信息(包括图片 id )
    5. 前端显示上传完成,显示预览图。
    6. 用户点击发微博的时候,传给后端的数据内容中图片以 id 表示

    一个例子:

    POST  /user/xxxx/status
    
    data: 
    
    {
        "content" : "微博内容",
        "pics" : [
            "picId-1",
            "picId-2",
            "picId-3"
        ],
    }
    

    而且,图片还是有顺序的。

    到正题了,后端该如何存储这条微博。

    方案分析

    下面是我想到的几种方案

    图片信息表结构:

    image_table

    1.新建一张微博-图片表

    微博表:

    x

    微博-图片表:

    x

    优点:

    1. 结构清晰,遵循范式
    2. 可以直接使用 SQL 查询

    缺点:

    1. 这种方案在查询的时候需要联合三张表来查
    2. 如果保证顺序的话,需要按照插入的时间戳排序或者增加一个字段,比如 order

    2.在微博表中新增一个字段存储图片信息

    微博表

    x

    2.1 pics 字段使用字符串,以分隔符隔开

    比如:

    pics = "picId1,picId2,picId3"
    
    优点:
    1. 不用新建表
    2. 保证了顺序问题
    缺点:
    1. 违反范式
    2. 需要先解析字符串,再查询

    2.2 pics 字段使用 json 格式

    比如:

    pics = [picId1,picId2,picId3]
    
    优点:
    1. 不用新建表
    2. 保证了顺序问题
    3. 相比 2.1 字符串,更优雅直观
    缺点:
    1. 违反范式
    2. 需要先解析字符串,再查询
    3. json 类型依赖特定数据库版本

    请教

    暂时就想到这几种方案。

    请问各位这几种方案孰优孰劣,选择哪种更好一点?

    还有没有更好的方案选择呢?

    第 1 条附言  ·  2019-05-03 05:29:33 +08:00
    对不起,图裂了,请看这张图

    https://s2.ax1x.com/2019/05/03/ENDKz9.png
    第 2 条附言  ·  2019-05-03 22:38:27 +08:00
    感谢各位的指教,准备使用方案 2.2,json 字段存储。这样,做修改功能的时候“增删图片,改变图片排序”比较容易一些。
    22 条回复    2019-05-04 01:15:06 +08:00
    zhengjian
        1
    zhengjian  
    OP
       2019-05-03 03:47:56 +08:00
    对不起,图裂了,请看这张图

    https://s2.ax1x.com/2019/05/03/ENDKz9.png
    zhengjian
        2
    zhengjian  
    OP
       2019-05-03 04:43:39 +08:00 via iPhone
    睡觉前又想到一个方案。

    图片表增加一个 status_id 字段,上传图片的时候为 null,当发布微博的时候再将微博 id 插入进去。

    顺序问题还要想想。
    emeab
        3
    emeab  
       2019-05-03 05:15:18 +08:00
    @zhengjian 顺序问题可以加一个字段 按照上传顺序 比如你上传第一张 Post 的时候加一个标识为第几张
    emeab
        4
    emeab  
       2019-05-03 05:19:05 +08:00
    @zhengjian 实现起来当然 2.2 更好。但是后续升级维护还是第一种更好
    zhengjian
        5
    zhengjian  
    OP
       2019-05-03 05:29:01 +08:00 via iPhone
    @emeab 嗯嗯,我觉得如果按照 1 的话顺序应该加个字段。

    其实还有编辑功能,编辑的时候有可能会修改顺序,或者增删图片。选方案 1 的话工作量还挺大的。
    GoLand
        6
    GoLand  
       2019-05-03 06:07:08 +08:00 via iPhone
    用 2.2。图片 ident 塞到信息序列化成字符串,可以前面加个 key。比如 { “ pics ”:[xx, xx]}。之后加其他额外信息往这个 map 里加就行了。好扩展。对于你的第一种方案,画蛇添足,因为图片一般没有查询场景吧?
    kevinlm
        7
    kevinlm  
       2019-05-03 06:30:00 +08:00 via iPhone
    图片地址存成一个字段,完事,地址本来就是唯一的。
    richard1122
        8
    richard1122  
       2019-05-03 09:59:11 +08:00
    图片应该很大概率是不需要单独查找的吧,用 方案 2。
    richard1122
        9
    richard1122  
       2019-05-03 10:00:19 +08:00
    另外应该用 json 方式,以后万一有特别的需求 mysql 还可以用 json 操作函数计算
    DavidNineRoc
        10
    DavidNineRoc  
       2019-05-03 10:09:26 +08:00
    说说方案吧:
    json 存储: (图片表)
    优势显然可见, 简单. 还有你的问题不会存在, 解析字符串再查询? 有想会查询图片呢? 解析的话, 这个无所谓, 像 redis 缓存一样是把整个数据缓存成字符串.

    一对多: (微博表, 图片表)
    你的更多可能是一对多, 步骤应该是这样. 前端选择图片,上传之后, 切记不要插入数据库. 因为如果你现在插入, 如果我刷新, 或者不发表文章了, 你的数据库记录是删除掉?反而麻烦了自己.
    正确做法: 前端选择图片 -> 云存储(本地存储)保存返回 url 数组 -> 前端直接根据 url 预览 -> 写微博点击发表 -> 先创建微博 -> 拿微博 id 新建图片表

    多对多: (微博表, 图片表, 关联表)
    这种表结构一定要确定好你的应用场景, 建议和一对多区分开. 这种做法常见的可以参考 wordpress, 一般有一个媒体库. 发微博之前一般先去媒体库上传图片, 等你去微博的时候, 可以选择之前上传过的所有图片(当然你也可以在新建微博的时候上传图片, 同理这些图片在以后也可以继续使用)
    kangzai50136
        11
    kangzai50136  
       2019-05-03 13:21:24 +08:00 via Android
    我最近也在做差不多的功能,我是用微博表,微博详情表和图片表。虽然表多,但结构清晰。
    xuanbg
        12
    xuanbg  
       2019-05-03 13:55:39 +08:00
    图片又不搜索,当然存成 json 最简单。MySQL5.6 就有 json 的支持了,哪怕更早版本,直接当做字符串存储也没问题,反正你并不需要在 sql 语句中解析。
    zhengjian
        13
    zhengjian  
    OP
       2019-05-03 15:21:29 +08:00
    @kangzai50136 #11 请问您的需求中需要保存顺序吗,是怎么解决顺序问题的呢?


    @DavidNineRoc #10 感谢您的耐心解答,对于你第二点中的解释,“前端选择图片 -> 云存储(本地存储)保存返回 url 数组”,在返回 url 数组的时候,因为图片会有其他信息和多个 url,这时后端是不是要设置一个过期时间缓存一下,当微博发送的时候再插入数据库,如果用户不发表了,这些图也就过期了。
    kangzai50136
        14
    kangzai50136  
       2019-05-03 18:57:41 +08:00 via Android
    @zhengjian 我是增加一个排序字段,排序由前端决定,接口接收参数时检验一下排序字段有没有重复,有则返回错误。{“ content ”:“我是一条新微博”,pics[{filename:xx,sort:1},{filename:yyy,sort:2}]},有兴趣可以加我企鹅大家讨论一下。
    zhengwhizz
        15
    zhengwhizz  
       2019-05-03 21:07:48 +08:00 via Android
    图片直接前端预览 /压缩( pica),发微博时才上传,避免用户取消发布、删除 /修改图片再请求接口(或者无用图片的存储,毕设可以忽略,产品的话这种操作不在少数, 云存储带宽和容量这些都是成本),至于多表或者 json 没太大区别,个人觉得 json 更方便,至于排序,加个修改时间戳,前端排一下即可。
    zhengjian
        16
    zhengjian  
    OP
       2019-05-03 22:40:56 +08:00
    @kangzai50136 #14 那您修改图片顺序的时候,操作量不会很大。


    @zhengwhizz #15 嗯嗯,一起上传的话,如果九张图,考虑到速度问题,还是选择了先上传图片。因为实际需求比微博的表单字段要多很多。
    kangzai50136
        17
    kangzai50136  
       2019-05-03 23:18:38 +08:00 via Android
    @zhengjian 为什么修改图片顺序操作量会很大呢
    zhengjian
        18
    zhengjian  
    OP
       2019-05-04 00:12:56 +08:00
    @kangzai50136 #17 抱歉,我以为你是存储的 微博-图片表。

    如果表结构为
    - weibo_id
    - pic_id
    - order

    更改顺序或增删图片,就要操作多条记录
    autogen
        19
    autogen  
       2019-05-04 00:34:15 +08:00
    一般都是这样设计的:


    微博表:
    - weibo_id *
    - content

    图片表:
    - pic_id
    - width
    - height
    - thumb
    - origin
    - weibo_id *



    而且图片服务器会返回一个图片 ID,根据这个 ID 来拼链接就行
    比如:图片 ID=aaaabbbbccccdddd,
    url 你自己拼就行: http://cdn1.cdn.com/aaaabbbbccccdddd_100x100.jpg



    -
    autogen
        20
    autogen  
       2019-05-04 00:41:52 +08:00
    代码:
    def getWeiboInfo(id):
    ....getBaseInfo(id)
    ....getImgInfo(id)
    ....return combine(base, imgs);

    -
    def getBaseInfo(id):
    ....if id in cache:
    ........return cache.get(id)
    ....return weibo.query(id)



    -
    autogen
        21
    autogen  
       2019-05-04 00:43:55 +08:00
    图片顺序,图片表加个 order 字段
    zhengjian
        22
    zhengjian  
    OP
       2019-05-04 01:15:06 +08:00
    @autogen #21 感谢详细讲解。这样的话是标准的一对多关系。当上传来图片的时候在图片表插入一条记录,weibo_id , order 设为 null,发布微博的时候再批量修改这几条图片记录的 weibo_id 和 order。新发布的时候逻辑很清晰。

    当修改的时候,对比当前图片数组与新的图片数组,需要分几种情况:
    1. 顺序变,更改 order
    2. 有增加,将 weibo_id 插入增加的那张图片的记录,并重新修改 order
    3. 有删除,将删除的图片那条记录 weibo_id 设为 null 或删除这条记录,并重新修改 order

    这样修改的时候,操作会比较复杂一点。

    希望我没理解错误。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3930 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 30ms · UTC 10:27 · PVG 18:27 · LAX 03:27 · JFK 06:27
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.