MySQL-一对一关系？

Question

我正在尝试在MySQL数据库中实现“一对一”的关系。例如，假设我有一个Users表和一个Accounts表。我想确保一个用户只能拥有一个帐户。每个用户只能有一个帐户。

我找到了两个解决方案，但是不知道该使用什么，还有其他选择。

第一个解决方案：

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    account_name VARCHAR(45) NOT NULL,
    user_id INT UNIQUE,
    PRIMARY KEY(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

在此示例中，我在指向用户主键的帐户中定义外键。然后，我使外键成为唯一键，因此帐户中不能有两个相同的用户。要联接表，我将使用以下查询：

SELECT * FROM users JOIN accounts ON users.id = accounts.user_id;

第二种解决方案：

DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;

CREATE TABLE users(
    id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE accounts(
    id INT NOT NULL AUTO_INCREMENT,
    account_name VARCHAR(45) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(id) REFERENCES users(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

在此示例中，我创建了一个外键，该外键从主键指向另一个表中的主键。由于默认情况下主键是UNIQUE，因此将这种关系一对一。要联接表，我可以使用以下命令：

SELECT * FROM users JOIN accounts ON users.id = accounts.id;

现在的问题：

• 在MySQL中创建一对一关系的最佳方法是什么？
• 除了这两个以外，还有其他解决方案吗？

我正在使用MySQL Workbench，当我在EER图中设计一对一关系并让MySQL
Workbench生成SQL代码时，我得到了一对多关系：S那就是让我感到困惑的：S

而且，如果我将这些解决方案中的任何一个导入到MySQL Workbench EER图中，它就会将关系识别为“一对多：S”，这也很令人困惑。

因此，在MySQL DDL中定义一对一关系的最佳方法是什么。有什么选择可以实现这一目标？

Answer 1

由于默认情况下主键是UNIQUE，因此使此关系一对一。

不，这使关系“一对零或一个”。那是您真正需要的吗？

如果 是 ，那么您的“第二个解决方案”更好：

• 更简单
• 占用较少的存储空间1（因此使缓存“更大”）
• 可以减少维护的索引2，这有利于数据处理，
• 并且（因为您使用的是InnoDB）自然会对数据进行聚类，因此，在一起的用户也将帐户存储在一起，这可能会有利于缓存局部性和某些类型的范围扫描。

顺便说一句，您需要创建accounts.id一个普通的整数（而不是自动递增）以使其起作用。

如果 否 ，请参见下文…

在MySQL中创建一对一关系的最佳方法是什么？

好吧，“最佳”是一个重载的词，但是“标准”解决方案将与任何其他数据库相同：将两个实体（在您的情况下为用户和帐户）放在同一物理表中。

除了这两个以外，还有其他解决方案吗？

从理论上讲，您可以在两个PK之间进行循环FK，但是这将需要 延迟 约束来解决“鸡与蛋”问题，不幸的是，MySQL不支持该约束。

而且，如果我将这些解决方案中的任何一个导入到MySQL Workbench EER图中，它就会将关系识别为一对多：S那也令人困惑。

我没有使用该特定建模工具的大量实践经验，但是我想这是因为它是“一对多”的，其中“很多”面通过使其唯一性而被限制为1。请记住，“很多”并不表示“
1或很多”，而是“ 0或很多”，因此“封顶”版本实际上表示“ 0或1”。

1
不仅在附加字段的存储费用中，而且在二级索引中也是如此。并且由于您使用的InnoDB总是对表进行聚类，因此请注意，聚簇表中的二级索引甚至比基于堆的表中的二级索引还要昂贵。

2 InnoDB需要外键索引。