如何设计MySQL表实现用户与项目多对多分配关系的记录与查询

你提到的两种方案都属于关系型数据库设计中的反模式，存在明确缺陷：

• 矩阵表方案：每新增一名用户就要修改表结构新增列，用户规模增长后表字段会过度膨胀，查询、维护成本极高，完全不具备可扩展性
• 项目表新增user_N列的方案：除了无法支持不确定数量的用户关联需求外，还会产生大量空值冗余，关联查询、数据统计的逻辑会非常复杂

推荐实现：多对多关联中间表 #

这是关系型数据库处理「一个实体关联多个同类型实体、反向也支持多关联」场景的业界标准方案，具体实现如下：
新建一张中间表，命名为project_user_assignments（也可简称为project_users），表结构仅需要3个核心字段：

• 主键id（可选，也可直接设置project_id + user_id为联合主键）
• project_id：外键字段，关联Projects表的主键
• user_id：外键字段，关联Users表的主键

方案核心优势 #

• 无关联数量限制：一个项目关联多少用户、一个用户参与多少项目都完全不受限，不需要修改任何表结构，仅需要在中间表增删对应数据行即可实现关联关系的变更
• 查询逻辑简洁：
  • 查指定项目下的所有关联用户：SELECT u.* FROM Users u JOIN project_user_assignments pua ON u.id = pua.user_id WHERE pua.project_id = 目标项目ID
  • 查指定用户参与的所有项目：SELECT p.* FROM Projects p JOIN project_user_assignments pua ON p.id = pua.project_id WHERE pua.user_id = 目标用户ID
• 可扩展性强：后续如果需要存储用户在对应项目中的角色、加入时间、权限等级等关联属性，直接在中间表新增字段即可，不需要改动原有Users和Projects业务表的结构

内容的提问来源于stack exchange，提问作者HarryBlake