MySQL - 一对一的关系？

Question

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

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

第一个解决方案:

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;

在这个例子中，我创建了一个外键，它从主键指向另一个表中的主键。由于默认情况下主键是唯一的，这使得这种关系是一对一的。要加入表格，我可以使用:

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

Since Primary Keys are UNIQUE by default, this makes this relation One to One.

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

如果是，那么您的“第二种解决方案”会更好:

• 更简单，
• 占用更少的存储空间¹(因此使缓存“更大”)
• 需要维护的索引更少²，这有利于数据操作，
• 并且(因为您使用的是 InnoDB)自然 clusters数据，因此靠近的用户也将他们的帐户存储在一起，这可能有利于缓存局部性和某些类型的范围扫描。

顺便说一句，您需要将 accounts.id 设为普通整数(不是自动增量)才能使其正常工作。

如果否，请参见下文...

What is the best way to create One to One relation in MySQL?

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

Are there any other solutions other than these two?

理论上，您可以在两个 PK 之间创建循环 FK，但这需要 deferred 约束来解决先有鸡还是先有蛋的问题，遗憾的是 MySQL 不支持。

And if I import any of these solutions into MySQL Workbench EER diagram, it recognizes relations as One to Many :S Thats also confusing.

我没有太多使用该特定建模工具的实际经验，但我猜这是因为它是“一对多”的，其中“多”方通过使其独特而被限制为 1。请记住，“many”并不是“1 or many”的意思，而是“0 or many”的意思，所以“capped”版本真正的意思是“0 or 1”。

---

¹ 不仅是附加字段的存储费用，还有二级索引。而且由于您使用的是 always clusters tables 的 InnoDB ，请注意，聚集表中的二级索引比基于堆的表中的开销更大。

² InnoDB requires indexes在外键上。