MySQL 内容业务核心表设计从用户到评论的关联建模前言1. 先设计关系再落地数据表2. 用户、头像与文章分开保存高频核心数据和附属信息2.1 用户表只保留登录与身份信息2.2 头像表保存描述信息不直接保存图片内容2.3 文章表用作者外键建立归属3. 点赞、收藏与评论处理多对多和回复层级3.1 点赞和收藏用联合主键避免重复关系3.2 评论表用自关联表示回复关系4. 标签与媒体资源补齐分类和扩展能力4.1 标签与文章再一次使用中间表4.2 媒体资源表记录资源属性和归属5. 常用 SQL 约束与索引速查总结前言内容类后端通常会有用户、头像、文章、点赞、收藏、评论、标签和媒体资源等数据。若把它们都塞进一张表字段会越来越多重复数据难以控制查询也会变慢。更合理的做法是一类核心实体一张表实体之间的关系通过外键或中间表表达常用查询再配合索引优化。本文以文章社区为例从表之间的关系出发依次说明各表为什么这样拆分、字段代表什么以及PRIMARY KEY、UNIQUE KEY、KEY、FOREIGN KEY和CONSTRAINT在建表时分别解决什么问题。表设计不是单纯写 SQL而是把业务规则固化为可校验、可查询的数据结构。1. 先设计关系再落地数据表先看核心关系一位用户可以发布多篇文章也可以留下多条评论、点赞和收藏一篇文章可以有多条评论、多个标签和多项媒体资源文章与标签、用户与文章互动都属于多对多关系。user ── avatar │ ├── post ── comment ── comment │ ├── user_like_post ── user │ ├── user_collect_post ── user │ ├── post_tag ── tag │ └── file │ └── file──表示一对多。例如一位用户可拥有多篇文章但一篇文章只对应一位作者外键应该放在“多”的一侧也就是post.userId。对于点赞、收藏和标签这类多对多关系必须增加中间表不能在某个字段内拼接多个 ID。业务规则表设计方式带来的效果用户名不能重复UNIQUE KEY注册时自动拒绝重名搜索更快文章必须关联已有用户外键post.userId避免出现不存在的作者 ID同一用户不能重复点赞联合主键userId, postId数据库直接保证关系唯一删除文章后不保留互动和评论ON DELETE CASCADE清理无归属的关联数据删除父评论后保留回复ON DELETE SET NULL回复内容仍可展示2. 用户、头像与文章分开保存高频核心数据和附属信息2.1 用户表只保留登录与身份信息用户表是高频访问表适合只保留id、name和password等核心字段。id是内部唯一身份name用于登录或检索password必须保存加密后的哈希值不能保存明文。头像、签名等可变信息拆出去后用户主表更轻后续扩展也更从容。CREATETABLEuser(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,passwordVARCHAR(255)NOTNULL,PRIMARYKEY(id),UNIQUEKEYuk_user_name(name))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;这里NOT NULL表示不能为空AUTO_INCREMENT表示插入时由数据库自动生成递增 IDPRIMARY KEY是每行的唯一标识UNIQUE KEY保证用户名不能重复。utf8mb4可以正确保存中文和 Emoji。2.2 头像表保存描述信息不直接保存图片内容图片应放在对象存储、CDN 或静态资源服务表中只保存mimetype、filename、size和所属用户 ID。这样查询用户资料时只需拿到资源标识数据库不必承担图片传输。CREATETABLEavatar(idINTNOTNULLAUTO_INCREMENT,mimetypeVARCHAR(255)NOTNULL,filenameVARCHAR(255)NOTNULL,sizeINTNOTNULL,userIdINTNOTNULL,PRIMARYKEY(id),KEYidx_avatar_user_id(userId),CONSTRAINTfk_avatar_userFOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;KEY idx_avatar_user_id是普通索引用于按用户查询头像。CONSTRAINT fk_avatar_user是具名约束名称fk_avatar_user便于排查和维护后面的FOREIGN KEY ... REFERENCES ...则规定avatar.userId必须引用已存在的user.id。当前结构允许一位用户有多条头像记录适合保存变更历史如果业务只保留一张当前头像可把userId设为唯一键。2.3 文章表用作者外键建立归属文章包含标题、正文和作者。title使用VARCHAR(255)适合较短标题content使用LONGTEXT适合较长正文userId是作者 ID。按作者查看文章是常见需求因此userId需要普通索引。CREATETABLEpost(idINTNOTNULLAUTO_INCREMENT,titleVARCHAR(255)NOTNULL,contentLONGTEXT,userIdINTDEFAULTNULL,PRIMARYKEY(id),KEYidx_post_user_id(userId),CONSTRAINTfk_post_userFOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;DEFAULT NULL表示作者可以暂时为空适合草稿或匿名内容若文章发布后必须有作者应改成NOT NULL。表中的CONSTRAINT不仅防止写入无效作者也让“文章属于用户”的规则在数据库层得到约束。3. 点赞、收藏与评论处理多对多和回复层级3.1 点赞和收藏用联合主键避免重复关系一位用户可点赞或收藏多篇文章一篇文章也可被多位用户操作这就是多对多关系。点赞表和收藏表的设计相同用两个外键连接用户与文章再用联合主键限制同一组合只能出现一次。CREATETABLEuser_like_post(userIdINTNOTNULL,postIdINTNOTNULL,PRIMARYKEY(userId,postId),KEYidx_like_post_id(postId),CONSTRAINTfk_like_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_like_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;CREATETABLEuser_collect_post(userIdINTNOTNULL,postIdINTNOTNULL,PRIMARYKEY(userId,postId),KEYidx_collect_post_id(postId),CONSTRAINTfk_collect_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_collect_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;联合主键(userId, postId)已经覆盖按userId查询的场景所以无需重复建立userId普通索引。postId位于联合索引的第二列统计一篇文章被点赞或收藏多少次时无法高效单独使用该联合索引因此额外建立postId索引。ON DELETE CASCADE表示文章删除后相关点赞和收藏自动删除ON UPDATE CASCADE表示被引用 ID 更新时同步更新。外键引用文章主键时应写REFERENCES post(id)引用不存在的列会导致建表失败。3.2 评论表用自关联表示回复关系评论既要关联文章和用户也要支持“评论的评论”。parentId引用同一张comment表的id这就是自关联。顶层评论没有父评论所以parentId可以为空。CREATETABLEcomment(idINTNOTNULLAUTO_INCREMENT,contentLONGTEXT,postIdINTNOTNULL,userIdINTNOTNULL,parentIdINTDEFAULTNULL,PRIMARYKEY(id),KEYidx_comment_post_id(postId),KEYidx_comment_user_id(userId),KEYidx_comment_parent_id(parentId),CONSTRAINTfk_comment_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_comment_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTfk_comment_parentFOREIGNKEY(parentId)REFERENCEScomment(id)ONDELETESETNULLONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;删除文章时评论应一并清理因此postId使用CASCADE删除父评论时回复仍有阅读价值因此parentId使用SET NULL。三个普通索引分别服务于按文章加载评论、按用户查评论和按父评论组装回复。4. 标签与媒体资源补齐分类和扩展能力4.1 标签与文章再一次使用中间表标签名必须唯一文章与标签仍是多对多关系。post_tag不保存额外正文只负责记录“哪篇文章拥有哪个标签”。CREATETABLEtag(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,PRIMARYKEY(id),UNIQUEKEYuk_tag_name(name))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATETABLEpost_tag(postIdINTNOTNULL,tagIdINTNOTNULL,PRIMARYKEY(postId,tagId),KEYidx_post_tag_tag_id(tagId),CONSTRAINTfk_post_tag_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTfk_post_tag_tagFOREIGNKEY(tagId)REFERENCEStag(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;联合主键以postId开头适合查“某篇文章有哪些标签”额外的tagId索引适合反向查“某个标签下有哪些文章”。这体现了联合索引的最左前缀原则查询条件从最左列开始索引才更容易被利用。4.2 媒体资源表记录资源属性和归属file表用于保存资源的名称、类型、大小、宽高以及扩展属性。userId不可为空用于追踪上传归属postId可以为空表示资源可先上传、后关联文章。metadata使用JSON保存不稳定的扩展属性但高频筛选字段仍应单独建列。CREATETABLEfile(idINTNOTNULLAUTO_INCREMENT,originalnameVARCHAR(255)NOTNULL,mimetypeVARCHAR(255)NOTNULL,filenameVARCHAR(255)NOTNULL,sizeINTNOTNULL,postIdINTDEFAULTNULL,userIdINTNOTNULL,widthSMALLINTNOTNULL,heightSMALLINTNOTNULL,metadataJSONDEFAULTNULL,PRIMARYKEY(id),KEYidx_file_post_id(postId),KEYidx_file_user_id(userId),CONSTRAINTfk_file_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_file_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETESETNULLONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;文章删除后postId置空而不是删除资源记录便于后续清理或继续追溯归属。字段命名应统一mimetype比容易混淆的mimitype更准确。5. 常用 SQL 约束与索引速查CONSTRAINT的重点不只是“写了一个名字”。它将外键规则显式命名使表之间的关系、更新策略和删除策略可以被准确识别和维护。MySQL 使用 InnoDB 引擎时才能正常使用外键和事务。语法作用示例场景NOT NULL不允许为空用户名、关联 ID 必填DEFAULT NULL未赋值时为空顶层评论没有parentIdPRIMARY KEY唯一标识一行用户、文章、评论的idUNIQUE KEY禁止重复值用户名、标签名KEY加速查询的普通索引postId、userId等查询条件FOREIGN KEY限制引用值必须存在评论必须关联已有文章CONSTRAINT命名并声明约束规则fk_comment_post等外键规则建表顺序也要注意先创建user再创建avatar和post最后创建点赞、收藏、评论、标签关联和资源表。因为外键依赖父表引用目标必须先存在。总结内容业务的表设计应围绕实体与关系展开用户、文章、标签和资源各自独立头像、文章和资源通过外键表达归属点赞、收藏、标签通过中间表表达多对多评论则通过自关联支持回复层级。主键负责唯一标识唯一键负责去重普通索引服务高频查询CONSTRAINT与外键共同守住引用完整性。把这些规则提前写进 DDL后续查询、扩展与数据治理都会更稳定。
MySQL 内容业务核心表设计：从用户到评论的关联建模
MySQL 内容业务核心表设计从用户到评论的关联建模前言1. 先设计关系再落地数据表2. 用户、头像与文章分开保存高频核心数据和附属信息2.1 用户表只保留登录与身份信息2.2 头像表保存描述信息不直接保存图片内容2.3 文章表用作者外键建立归属3. 点赞、收藏与评论处理多对多和回复层级3.1 点赞和收藏用联合主键避免重复关系3.2 评论表用自关联表示回复关系4. 标签与媒体资源补齐分类和扩展能力4.1 标签与文章再一次使用中间表4.2 媒体资源表记录资源属性和归属5. 常用 SQL 约束与索引速查总结前言内容类后端通常会有用户、头像、文章、点赞、收藏、评论、标签和媒体资源等数据。若把它们都塞进一张表字段会越来越多重复数据难以控制查询也会变慢。更合理的做法是一类核心实体一张表实体之间的关系通过外键或中间表表达常用查询再配合索引优化。本文以文章社区为例从表之间的关系出发依次说明各表为什么这样拆分、字段代表什么以及PRIMARY KEY、UNIQUE KEY、KEY、FOREIGN KEY和CONSTRAINT在建表时分别解决什么问题。表设计不是单纯写 SQL而是把业务规则固化为可校验、可查询的数据结构。1. 先设计关系再落地数据表先看核心关系一位用户可以发布多篇文章也可以留下多条评论、点赞和收藏一篇文章可以有多条评论、多个标签和多项媒体资源文章与标签、用户与文章互动都属于多对多关系。user ── avatar │ ├── post ── comment ── comment │ ├── user_like_post ── user │ ├── user_collect_post ── user │ ├── post_tag ── tag │ └── file │ └── file──表示一对多。例如一位用户可拥有多篇文章但一篇文章只对应一位作者外键应该放在“多”的一侧也就是post.userId。对于点赞、收藏和标签这类多对多关系必须增加中间表不能在某个字段内拼接多个 ID。业务规则表设计方式带来的效果用户名不能重复UNIQUE KEY注册时自动拒绝重名搜索更快文章必须关联已有用户外键post.userId避免出现不存在的作者 ID同一用户不能重复点赞联合主键userId, postId数据库直接保证关系唯一删除文章后不保留互动和评论ON DELETE CASCADE清理无归属的关联数据删除父评论后保留回复ON DELETE SET NULL回复内容仍可展示2. 用户、头像与文章分开保存高频核心数据和附属信息2.1 用户表只保留登录与身份信息用户表是高频访问表适合只保留id、name和password等核心字段。id是内部唯一身份name用于登录或检索password必须保存加密后的哈希值不能保存明文。头像、签名等可变信息拆出去后用户主表更轻后续扩展也更从容。CREATETABLEuser(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,passwordVARCHAR(255)NOTNULL,PRIMARYKEY(id),UNIQUEKEYuk_user_name(name))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;这里NOT NULL表示不能为空AUTO_INCREMENT表示插入时由数据库自动生成递增 IDPRIMARY KEY是每行的唯一标识UNIQUE KEY保证用户名不能重复。utf8mb4可以正确保存中文和 Emoji。2.2 头像表保存描述信息不直接保存图片内容图片应放在对象存储、CDN 或静态资源服务表中只保存mimetype、filename、size和所属用户 ID。这样查询用户资料时只需拿到资源标识数据库不必承担图片传输。CREATETABLEavatar(idINTNOTNULLAUTO_INCREMENT,mimetypeVARCHAR(255)NOTNULL,filenameVARCHAR(255)NOTNULL,sizeINTNOTNULL,userIdINTNOTNULL,PRIMARYKEY(id),KEYidx_avatar_user_id(userId),CONSTRAINTfk_avatar_userFOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;KEY idx_avatar_user_id是普通索引用于按用户查询头像。CONSTRAINT fk_avatar_user是具名约束名称fk_avatar_user便于排查和维护后面的FOREIGN KEY ... REFERENCES ...则规定avatar.userId必须引用已存在的user.id。当前结构允许一位用户有多条头像记录适合保存变更历史如果业务只保留一张当前头像可把userId设为唯一键。2.3 文章表用作者外键建立归属文章包含标题、正文和作者。title使用VARCHAR(255)适合较短标题content使用LONGTEXT适合较长正文userId是作者 ID。按作者查看文章是常见需求因此userId需要普通索引。CREATETABLEpost(idINTNOTNULLAUTO_INCREMENT,titleVARCHAR(255)NOTNULL,contentLONGTEXT,userIdINTDEFAULTNULL,PRIMARYKEY(id),KEYidx_post_user_id(userId),CONSTRAINTfk_post_userFOREIGNKEY(userId)REFERENCESuser(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;DEFAULT NULL表示作者可以暂时为空适合草稿或匿名内容若文章发布后必须有作者应改成NOT NULL。表中的CONSTRAINT不仅防止写入无效作者也让“文章属于用户”的规则在数据库层得到约束。3. 点赞、收藏与评论处理多对多和回复层级3.1 点赞和收藏用联合主键避免重复关系一位用户可点赞或收藏多篇文章一篇文章也可被多位用户操作这就是多对多关系。点赞表和收藏表的设计相同用两个外键连接用户与文章再用联合主键限制同一组合只能出现一次。CREATETABLEuser_like_post(userIdINTNOTNULL,postIdINTNOTNULL,PRIMARYKEY(userId,postId),KEYidx_like_post_id(postId),CONSTRAINTfk_like_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_like_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;CREATETABLEuser_collect_post(userIdINTNOTNULL,postIdINTNOTNULL,PRIMARYKEY(userId,postId),KEYidx_collect_post_id(postId),CONSTRAINTfk_collect_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_collect_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;联合主键(userId, postId)已经覆盖按userId查询的场景所以无需重复建立userId普通索引。postId位于联合索引的第二列统计一篇文章被点赞或收藏多少次时无法高效单独使用该联合索引因此额外建立postId索引。ON DELETE CASCADE表示文章删除后相关点赞和收藏自动删除ON UPDATE CASCADE表示被引用 ID 更新时同步更新。外键引用文章主键时应写REFERENCES post(id)引用不存在的列会导致建表失败。3.2 评论表用自关联表示回复关系评论既要关联文章和用户也要支持“评论的评论”。parentId引用同一张comment表的id这就是自关联。顶层评论没有父评论所以parentId可以为空。CREATETABLEcomment(idINTNOTNULLAUTO_INCREMENT,contentLONGTEXT,postIdINTNOTNULL,userIdINTNOTNULL,parentIdINTDEFAULTNULL,PRIMARYKEY(id),KEYidx_comment_post_id(postId),KEYidx_comment_user_id(userId),KEYidx_comment_parent_id(parentId),CONSTRAINTfk_comment_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_comment_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTfk_comment_parentFOREIGNKEY(parentId)REFERENCEScomment(id)ONDELETESETNULLONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;删除文章时评论应一并清理因此postId使用CASCADE删除父评论时回复仍有阅读价值因此parentId使用SET NULL。三个普通索引分别服务于按文章加载评论、按用户查评论和按父评论组装回复。4. 标签与媒体资源补齐分类和扩展能力4.1 标签与文章再一次使用中间表标签名必须唯一文章与标签仍是多对多关系。post_tag不保存额外正文只负责记录“哪篇文章拥有哪个标签”。CREATETABLEtag(idINTNOTNULLAUTO_INCREMENT,nameVARCHAR(255)NOTNULL,PRIMARYKEY(id),UNIQUEKEYuk_tag_name(name))ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;CREATETABLEpost_tag(postIdINTNOTNULL,tagIdINTNOTNULL,PRIMARYKEY(postId,tagId),KEYidx_post_tag_tag_id(tagId),CONSTRAINTfk_post_tag_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTfk_post_tag_tagFOREIGNKEY(tagId)REFERENCEStag(id)ONDELETECASCADEONUPDATECASCADE)ENGINEInnoDB;联合主键以postId开头适合查“某篇文章有哪些标签”额外的tagId索引适合反向查“某个标签下有哪些文章”。这体现了联合索引的最左前缀原则查询条件从最左列开始索引才更容易被利用。4.2 媒体资源表记录资源属性和归属file表用于保存资源的名称、类型、大小、宽高以及扩展属性。userId不可为空用于追踪上传归属postId可以为空表示资源可先上传、后关联文章。metadata使用JSON保存不稳定的扩展属性但高频筛选字段仍应单独建列。CREATETABLEfile(idINTNOTNULLAUTO_INCREMENT,originalnameVARCHAR(255)NOTNULL,mimetypeVARCHAR(255)NOTNULL,filenameVARCHAR(255)NOTNULL,sizeINTNOTNULL,postIdINTDEFAULTNULL,userIdINTNOTNULL,widthSMALLINTNOTNULL,heightSMALLINTNOTNULL,metadataJSONDEFAULTNULL,PRIMARYKEY(id),KEYidx_file_post_id(postId),KEYidx_file_user_id(userId),CONSTRAINTfk_file_userFOREIGNKEY(userId)REFERENCESuser(id),CONSTRAINTfk_file_postFOREIGNKEY(postId)REFERENCESpost(id)ONDELETESETNULLONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ci;文章删除后postId置空而不是删除资源记录便于后续清理或继续追溯归属。字段命名应统一mimetype比容易混淆的mimitype更准确。5. 常用 SQL 约束与索引速查CONSTRAINT的重点不只是“写了一个名字”。它将外键规则显式命名使表之间的关系、更新策略和删除策略可以被准确识别和维护。MySQL 使用 InnoDB 引擎时才能正常使用外键和事务。语法作用示例场景NOT NULL不允许为空用户名、关联 ID 必填DEFAULT NULL未赋值时为空顶层评论没有parentIdPRIMARY KEY唯一标识一行用户、文章、评论的idUNIQUE KEY禁止重复值用户名、标签名KEY加速查询的普通索引postId、userId等查询条件FOREIGN KEY限制引用值必须存在评论必须关联已有文章CONSTRAINT命名并声明约束规则fk_comment_post等外键规则建表顺序也要注意先创建user再创建avatar和post最后创建点赞、收藏、评论、标签关联和资源表。因为外键依赖父表引用目标必须先存在。总结内容业务的表设计应围绕实体与关系展开用户、文章、标签和资源各自独立头像、文章和资源通过外键表达归属点赞、收藏、标签通过中间表表达多对多评论则通过自关联支持回复层级。主键负责唯一标识唯一键负责去重普通索引服务高频查询CONSTRAINT与外键共同守住引用完整性。把这些规则提前写进 DDL后续查询、扩展与数据治理都会更稳定。