mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

9.1 一对一关系

在数据库设计中，表与表之间的关系可以分为三种：一对一（1:1）、一对多（1:N）和多对多（N:N）。本节我们将深入探讨一对一关系，理解它的概念、应用场景以及如何在MySQL中实现。

一、什么是一对一关系？

一对一关系是指表A中的每一条记录，在表B中最多只有一条记录与之对应；反之，表B中的每一条记录，在表A中也最多只有一条记录与之对应。简单来说，两个表中的记录是一一对应的。

例如：

一个用户对应一个用户档案（一个用户只能有一个档案，一个档案也只属于一个用户）。
一个员工对应一个工牌（一个员工只有一个工牌，一个工牌也只发给一个员工）。

在实际生活中，这种关系通常意味着两个实体之间具有“属于”或“拥有”的关系，且这种关系是互斥且完整的。

二、一对一关系的应用场景

一对一关系虽然不如一对多普遍，但在某些场景下非常有用：

场景

说明

垂直分表（拆分大表）

当一张表的字段过多，且部分字段使用频率较低或数据量较大时，可以将这些字段拆分到另一张表中，形成一对一关系。例如：用户表中包含常用的基本信息和不常用的扩展信息。

隐藏敏感信息

将敏感字段（如身份证号、密码、薪资）拆分到独立的表中，并授予不同的访问权限，提高安全性。

扩展已有表

在系统后期需要给原表增加一些可选字段，但又不希望改动原表结构时，可以创建一张扩展表与之关联。

实体子类化

在面向对象设计中，一个父类可以有多个子类，数据库可以用一对一关系实现。例如：动物表与猫表、狗表等。

三、实现一对一关系的两种方式

在MySQL中，实现一对一关系主要有两种设计方法：

方法一：主键关联（共享主键）

让两张表使用相同的主键值，子表的主键同时也是外键，引用父表的主键。这种方式确保了每一条子表记录都唯一地对应一条父表记录。

示例：用户表（users）与用户档案表（user_profiles）。

-- 父表：用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
);

-- 子表：用户档案表，其主键同时也是外键，引用 users(user_id)
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,  -- 主键，同时也是外键
    full_name VARCHAR(100),
    avatar_url VARCHAR(255),
    birth_date DATE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

优点：

结构清晰，主键直接体现了关联关系。
查询时可以使用 JOIN 或直接通过主键获取数据。

缺点：

子表的主键值必须由父表提供，不能独立生成（通常由应用程序在插入父表后获取最后ID再插入子表）。

方法二：唯一外键关联

在子表中增加一个外键列，指向父表的主键，并给该外键列添加唯一约束（UNIQUE），从而保证子表中的每一条记录只对应父表的一条记录。

示例：还是用户与档案，但这次使用独立的主键。

-- 父表：用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE
);

-- 子表：用户档案表，使用独立主键，外键 user_id 加唯一约束
CREATE TABLE user_profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL UNIQUE,  -- 外键 + 唯一约束
    full_name VARCHAR(100),
    avatar_url VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

优点：

子表有独立的主键，可以自由管理。
仍然保证了一对一的完整性。

缺点：

比主键关联多了一个列，但通常影响不大。

两种方式的对比

特点

主键关联

唯一外键关联

子表主键

无独立主键，与父表共享

有独立主键

外键约束

是，主键即外键

是，额外加唯一约束

适用场景

紧密依赖父表，如垂直分表

相对独立，但需要一对一关系

插入顺序

必须先插入父表，再插入子表

同左

在实际开发中，两种方式都可以，选择取决于个人偏好和具体业务需求。主键关联更直接地体现了一对一的本质，而唯一外键关联则更灵活，适合子表需要独立主键的场景。

四、完整示例：员工与工牌

假设公司需要管理员工信息和工牌信息。一个员工只能有一个工牌，一个工牌只能属于一个员工。

使用唯一外键关联实现：

-- 员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_no VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50)
);

-- 工牌表，与员工一对一
CREATE TABLE badges (
    badge_id INT PRIMARY KEY AUTO_INCREMENT,
    badge_code VARCHAR(50) NOT NULL UNIQUE,
    emp_id INT NOT NULL UNIQUE,  -- 外键 + 唯一
    issue_date DATE,
    FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 插入数据
INSERT INTO employees (emp_no, name, department) VALUES
('E001', '张三', '技术部'),
('E002', '李四', '市场部');

INSERT INTO badges (badge_code, emp_id, issue_date) VALUES
('B001', 1, '2023-01-10'),
('B002', 2, '2023-02-15');

-- 查询员工及其工牌信息
SELECT e.name, e.department, b.badge_code, b.issue_date
FROM employees e
JOIN badges b ON e.emp_id = b.emp_id;

五、注意事项

外键约束：强烈建议添加外键约束，以维护数据的参照完整性。同时，根据业务需求设置合适的 ON DELETE 和 ON UPDATE 行为（如 CASCADE、SET NULL 等）。
唯一约束：在唯一外键关联中，务必为外键列加上唯一约束，否则会退化成一对多关系。
双向存在性：一对一关系通常意味着双方都可能存在，但也可能存在“可选”的一对一。例如，一个员工不一定有工牌（此时外键列可设为允许 NULL，并去掉唯一约束？但这样就不是一对一了，而是零或一）。如果允许一方缺失，需要根据业务决定是否允许外键为空。
查询性能：一对一关联查询通常需要 JOIN，在数据量大时注意索引的建立。

六、练习与思考

设计一个“车辆”与“车牌”的一对一关系。车辆表（车辆ID、品牌、型号），车牌表（车牌ID、车牌号、车辆ID）。请用两种方式实现。
思考：如果某张表需要与多个其他表建立一对一关系（比如用户表同时与档案表、安全设置表一对一），应该采用哪种方式更合适？
尝试写出 SQL 语句，查询所有拥有档案的用户信息（使用 INNER JOIN）。

七、总结

一对一关系表示两个表中的记录一一对应。
常见应用：垂直分表、隐藏敏感信息、扩展表等。
实现方式有两种：主键关联和唯一外键关联，都需结合外键和唯一约束。
设计时需考虑业务是否需要强制双方都存在，以及级联操作的行为。