SQL 多对多关系如何设计中间表？

多对多关系必须通过中间表实现，其结构含两个外键字段（如user_id、role_id），建议设联合主键或自增ID加唯一约束，并为外键建索引；需定义外键约束，级联策略依业务而定；当关系具属性时，中间表升格为业务实体。

多对多关系必须通过中间表（也叫关联表、桥接表）来实现，不能直接在两个主表之间加外键解决。

中间表的基本结构

中间表通常只包含两个外键字段，分别指向两个主表的主键。不建议额外添加业务字段，除非有明确需求（比如记录关系建立时间、状态等）。

• 字段命名建议用 表名_id 形式，例如 user_id 和 role_id
• 两个字段共同组成联合主键，或单独设一个自增 id 主键 + 唯一约束（UNIQUE(user_id, role_id)）
• 为两个外键各自建立索引，提升 JOIN 和 WHERE 查询效率

外键约束与级联操作

中间表应定义外键，确保数据一致性。是否启用 O

• 删除用户时自动清除其所有角色关联？适合强依赖场景（如权限系统）
• 删除角色时不删中间记录？适合宽松管理（如标签系统，删标签不影响内容）
• 更安全的做法是设为 ON DELETE RESTRICT，由应用层控制删除逻辑

常见扩展设计

当关系本身携带属性时，中间表就不再是“纯关联”，而是有业务含义的实体：

• 学生选课：中间表可增加 grade（成绩）、semester（学期）
• 订单商品：中间表就是订单明细表，含 quantity、price_at_order
• 此时中间表通常有自己的主键（如 id），并可能被其他表引用

查询示例（以用户-角色为例）

查某用户的所有角色：

SELECT r.name FROM users u
  JOIN user_roles ur ON u.id = ur.user_id
  JOIN roles r ON ur.role_id = r.id
WHERE u.id = 123;

查某个角色下的所有用户，只需交换 JOIN 顺序即可。使用中间表后，这类查询清晰且高效。