表关系与 E-R 图

约 971 字大约 3 分钟

布欧-Lewyon

2026-05-15

数据表之间通过关系（Relationship）来反映真实世界中实体的关联。理解三种基本关系类型，是设计合理数据库结构的前提。

三种基本关系

一对一（1:1）

A 表的一行对应 B 表的一行。设计举例：用户表与用户扩展信息表。

CREATE TABLE user (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,   -- 主键同时是外键
    avatar  VARCHAR(200),
    bio     TEXT,
    FOREIGN KEY (user_id) REFERENCES user(id)
);

特征：user_profile 的主键同时也是外键，确保每行一对一。

适用场景：

表字段太多（> 20~30 列）时按业务模块垂直拆分
敏感字段单独存（如身份证号、支付信息）
大字段（BLOB/TEXT）单独存放，避免拖慢主表的行读取

一对多（1:N）

最常见的关系。A 表的一行对应 B 表的多行。

CREATE TABLE department (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employee (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,                        -- 外键，指向 department.id
    INDEX idx_dept (dept_id)
    -- 可选的 FOREIGN KEY，生产环境常由应用层保证
);

多对多（M:N）

A 表的一行对应 B 表的多行，反之亦然。通过中间表/关联表实现。

CREATE TABLE student (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE course (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE student_course (
    student_id INT,
    course_id  INT,
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id),
    INDEX idx_course (course_id)
    -- FOREIGN KEY 引用 student(id) 和 course(id)
);

中间表可以附加业务字段（如 enrolled_at、score），使其变为一张业务表。

外键的使用与争议

使用外键的优缺点

优点	缺点
强引用完整性，防止脏数据	写入时额外校验，降低性能
级联操作（CASCADE）简化代码	分库分表时无法跨库使用
关系自文档化	高并发时易产生锁争用

生产实践建议

金融/财务系统：使用外键保障数据强一致性。
互联网高并发业务：通常不用外键，由应用层保证关联完整性。
中间表：多对多场景下的中间表可以设外键，因为它通常是小表、低频修改。

使用 Mermaid 绘制 E-R 图

在 Markdown 中用 mermaid 语法绘制关系图，便于在设计文档中展示：

```mermaid
erDiagram
    User ||--o{ Order : places
    User {
        int id PK
        string name
    }
    Order ||--|{ OrderItem : contains
    Order {
        int id PK
        int user_id FK
        datetime created_at
    }
    OrderItem {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
    }
    Product ||--o{ OrderItem : appears_in
    Product {
        int id PK
        string name
        decimal price
    }
```

E-R 图标记说明：

符号	含义
`
`
`}o--o{`	多对多（M 对 N）
`PK`	主键
`FK`	外键

实体命名的实用建议

表名：英文小写 + 下划线，单数（MySQL 约定），如 user、order_item。
主键列：统一叫 id，自增或雪花 ID。
外键列：引用表名 + _id，如 user_id、order_id。
中间表：用两个表名拼接，如 student_course、user_role。

小结

一对一用同一主键 + 外键实现；一对多在外键表中存父表 ID；多对多通过中间表实现。
外键在金融场景推荐使用，高并发互联网业务通常由应用层保证完整性。
E-R 图（Mermaid erDiagram）是表达表关系的高效图形式。
表命名保持一致性：表名单数小写，主键 id，外键 <table>_id。

上一节：三大范式与反范式下一节：索引基础与 B+ Tree