MySQL / 系统课时

一对一、一对多与多对多

一对一、一对多与多对多

表关系描述的是不同实体之间如何关联。理解一对一、一对多和多对多后,就能判断外键应该放在哪里、什么时候需要中间表,并为后续多表查询建立清晰的数据模型。

学习目标

  • 区分一对一、一对多和多对多三类常见关系
  • 根据业务关系确定外键和中间表的位置
  • 使用 MySQL 表结构表达三类关系

从业务关系判断基数

关系类型关注的是一条记录最多能对应另一张表中的多少条记录。例如一个用户只有一份用户档案,可以建模为一对一;一个部门拥有多个员工,而一个员工只属于一个部门,是典型的一对多;一名学生可以选择多门课程,一门课程也可以被多名学生选择,则属于多对多。

三类表关系及其数据库建模方式

一对一使用唯一外键

一对一通常把外键放在依赖性更强的一方,并使用 UNIQUE 限制同一个主记录只能出现一次。下面用用户与用户档案演示这种约束。

sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE user_profiles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL UNIQUE,
    nickname VARCHAR(50),
    CONSTRAINT fk_profile_user
        FOREIGN KEY (user_id) REFERENCES users(id)
);

预期结果:两张表创建成功,并且 user_profiles.user_id 同时具有外键和唯一性约束,因此同一个用户不能拥有两条档案记录。

text
Query OK

实际客户端显示的执行耗时等信息可能不同。

一对多把外键放在多的一侧

一对多是业务系统中最常见的关系。部门无需保存员工 ID 列表,而是在员工表保存 department_id。这样每个员工只引用一个部门,同一个部门 ID 却可以被多条员工记录引用。

sql
CREATE TABLE departments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department_id BIGINT NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id) REFERENCES departments(id)
);

预期结果:多个 employees 记录可以保存相同的 department_id,形成一个部门对应多个员工的关系。

text
Query OK

多对多需要中间表

关系两侧都可能出现多条对应记录时,不能简单地在任意一张主表放一个外键,因为单个字段无法自然保存多个关联 ID。常见做法是新增中间表,每一行只表示一组关联。

sql
CREATE TABLE students (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE student_courses (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

预期结果:同一学生可以在 student_courses 中关联多门课程,同一课程也可以关联多名学生;联合主键还能避免同一选课关系被重复插入。

text
Query OK

建模时可以先问两个问题:左侧一条记录最多对应右侧多少条,右侧一条记录最多又对应左侧多少条。答案是 1 和 1 时通常是一对一,1 和 N 是一对多,N 和 N 则应优先考虑中间表。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

一对一、一对多与多对多

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通