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

问题描述

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

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;

在此示例中，我创建了一个外键，该外键从主键指向另一个表中的主键.由于默认情况下主键是UNIQUE，因此使此关系一对一.要联接表，我可以使用以下方法:

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?

推荐答案

由于默认情况下主键是UNIQUE，因此这种关系是一对一的.

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.

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

If no, see below...

在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，但这将需要 deferred 约束来解决鸡与蛋"问题，不幸的是，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.请记住，很多"并不表示"1或很多"，而是"0或很多"，因此封顶"版本实际上表示"0或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屋！