MySQL - 一对一的关系? [英] MySQL - One To One Relationship?

问题描述

我正在努力实现一对一"MySQL 数据库中的关系.例如，假设我有一个用户表和一个帐户表.我想确保一个用户只能拥有一个帐户.并且每个用户只能有一个帐户.

I'm trying to achieve a "One to one" relationship in a MySQL database. For example, let's say I have a Users table and an Accounts table. I want to be sure that a User can have only one Account. And that there can be only one Account per User.

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

I found two solutions for this but don't know what to use, and are there any other options.

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;

在这个例子中，我在账户中定义了指向用户主键的外键.然后我使外键唯一，因此帐户中不能有两个相同的用户.要连接表，我将使用此查询:

In this example, I define the foreign key in accounts pointing to the primary key in users. And then I make foreign key UNIQUE, so there can't be two identical users in accounts. To join tables I would use this query:

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;

在此示例中，我创建了一个外键，该外键从主键指向另一个表中的主键.由于默认情况下主键是唯一的，这使得这种关系是一对一的.要连接表，我可以使用它:

In this example, I create a foreign key that points from the primary key to a primary key in another table. Since Primary Keys are UNIQUE by default, this makes this relation One to One. To join tables I can use this:

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

现在的问题:

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

我正在使用 MySQL Workbench，当我在 EER 图中设计一对一关系并让 MySQL Workbench 生成 SQL 代码时，我得到一对多关系:S 这让我很困惑 :S

I'm using MySQL Workbench, and when I design One To One relation in EER diagram and let MySQL Workbench produce SQL code, I get One to Many relation :S That's what's confusing me :S

如果我将这些解决方案中的任何一个导入 MySQL Workbench EER 图表，它会将关系识别为一对多 :S 这也令人困惑.

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

那么，在 MySQL DDL 中定义一对一关系的最佳方法是什么.有哪些选择可以实现这一目标?

So, what would be the best way to define One to One relation in MySQL DDL. And what options are there to achieve this?

推荐答案

由于默认情况下主键是唯一的，这使得这种关系是一对一的.

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

不，这使得关系一对零或一".那是您真正需要的吗?

No, that makes the relation "one to zero or one". Is that what you actually need?

如果是，那么您的第二个解决方案"更好:

If yes, then then your "second solution" is better:

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

• it's simpler,
• takes less storage¹ (and therefore makes cache "larger")
• hes less indexes to maintain², which benefits data manipulation,
• and (since you are using InnoDB) naturally clusters the data, so users that are close together will have their accounts stored close together as well, which may benefit cache locality and certain kinds of range scans.

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

BTW, you'll need to make accounts.id an ordinary integer (not auto-increment) for this to work.

如果否，请看下文...

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

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

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

Well, "best" is an overloaded word, but the "standard" solution would be the same as in any other database: put both entities (user and account in your case) in the same physical table.

除了这两个之外还有其他解决方案吗?

Are there any other solutions other than these two?

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

Theoretically, you could make circular FKs between the two PKs, but that would require deferred constraints to resolve the chicken-and-egg problem, which are unfortunately not supported under MySQL.

如果我将这些解决方案中的任何一个导入 MySQL Workbench EER 图表，它会将关系识别为一对多 :S 这也令人困惑.

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".

I don't have much practical experience with that particular modeling tool, but I'm guessing that's because it is "one to many" where "many" side was capped at 1 by making it unique. Please remember that "many" doesn't mean "1 or many", it means "0 or many", so the "capped" version really means "0 or 1".

¹ 不仅是附加字段的存储开销，还有二级索引的存储开销.并且由于您使用的是 InnoDB，它始终对表进行聚类，请注意辅助聚集表中的索引比基于堆的表中的索引开销更大.

¹ Not just in the storage expense for the additional field, but for the secondary index as well. And since you are using InnoDB which always clusters tables, beware that secondary indexes are even more expensive in clustered tables than they are in heap-based tables.

² InnoDB 需要外键索引.

² InnoDB requires indexes on foreign keys.

这篇关于MySQL - 一对一的关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！