MySQL - 一对一的关系？

Question

提问by Limeni

I'm trying to achieve One To One relation in MySQL database. For example, let's say I have Users table and Accounts table. And I want to be sure there is User can have only one account. And that there can be only one Account per user.

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

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

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

First solution:

第一个解决方案：

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;

Second solution:

第二种解决方案：

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;

Now the questions:

现在的问题：

What is the best way to create One to One relation in MySQL?
Are there any other solutions other than these two?

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

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 图中设计一对一关系并让 MySQL Workbench 生成 SQL 代码时，我得到一对多关系：S 这让我很困惑：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 Workbench EER 图表，它会将关系识别为一对多 :S 这也令人困惑。

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

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

Answer 1

采纳答案by Branko Dimitrijevic

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:

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

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

更简单，
占用更少的存储空间¹（因此使缓存“更大”）
维护^{2 的}索引较少，这有利于数据操作，
并且（因为您使用的是 InnoDB）自然地对数据进行聚类，因此距离较近的用户也会将他们的帐户存储在一起，这可能有利于缓存局部性和某些类型的范围扫描。

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

顺便说一句，您需要创建accounts.id一个普通整数（不是自动递增）才能使其工作。

If no, see below...

如果没有，请看下面...

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

在 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?

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

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

从理论上讲，您可以在两个 PK 之间创建循环 FK，但这需要延迟约束来解决先有鸡还是先有蛋的问题，不幸的是，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.

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

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

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

¹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 indexeson foreign keys.

²InnoDB需要外键索引。

Answer 2

回答by Salman A

Your first approach creates two candidate keys in the accounts table: idand user_id.

您的第一种方法在帐户表中创建两个候选键：id和user_id。

I therefore suggest the second approach i.e. using the foreign key as the primary key. This:

因此，我建议使用第二种方法，即使用外键作为主键。这个：

uses one less column
allows you to uniquely identify each row
allows you to match account with user

少用一列
允许您唯一标识每一行
允许您将帐户与用户匹配

Answer 3

回答by Jürgen Steinblock

What about the following approach

下面的方法呢

Create Table user

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Table account with a unique index on user_idand account_idwith a foreign key relation to user/account and a primary key on user_id and account_id

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Table user2account

CREATE TABLE `user2account` (
  `user_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`account_id`),
  UNIQUE KEY `FK_account_idx` (`account_id`),
  UNIQUE KEY `FK_user_idx` (`user_id`),
  CONSTRAINT `FK_account` FOREIGN KEY (`account_id`) REFERENCES         `account` (`id`),
  CONSTRAINT `FK_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表用户

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建具有唯一索引的表帐户，user_id并account_id具有与用户/帐户的外键关系和主键user_id and account_id

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建表 user2account

CREATE TABLE `user2account` (
  `user_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`account_id`),
  UNIQUE KEY `FK_account_idx` (`account_id`),
  UNIQUE KEY `FK_user_idx` (`user_id`),
  CONSTRAINT `FK_account` FOREIGN KEY (`account_id`) REFERENCES         `account` (`id`),
  CONSTRAINT `FK_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

While this solution has the largest footprint in the database, there are some advantages.

虽然此解决方案在数据库中占用的空间最大，但也有一些优势。

Putting the FK_Key in either the user table or the account table is something that I expect to be a one to many releation (user has many accounts ...)
While this user2accountapproach is mainly used to define a many to many relationship, adding a UNIQUE constraint on user_idand on account_idwill prevent creating something else than a one to one relation.

将 FK_Key 放在用户表或帐户表中是我希望成为一对多关系的东西（用户有很多帐户......）
虽然此user2account方法主要用于定义多对多关系，但在 onuser_id和 on 上添加 UNIQUE 约束account_id将防止创建除一对一关系之外的其他内容。

The main advantage I see in this solution is that you can divide the work in different code layers or departements in a company

我在这个解决方案中看到的主要优点是您可以将工作划分到不同的代码层或公司的部门

Department A is responsible for creating users, this is possible even without write permission to accounts table
Departement B is responsible for creating accounts, this is possible even without write permission to user table
Departement C is responsible for creating the mapping, this is possible even without write permission to user or account table
Once Departement C has created a mapping neither the user nor the account can be deleted by departement A or B without asking departement C to delete the mapping first.

A部门负责创建用户，即使没有对accounts表的写权限也可以
部门B负责创建帐户，即使没有对用户表的写权限也是可能的
部门 C 负责创建映射，即使没有对用户或帐户表的写权限也是可能的
一旦部门 C 创建了映射，则部门 A 或 B 都不能删除用户和帐户，而无需先要求部门 C 删除映射。

MySQL - 一对一的关系？

提问by Limeni

First solution:

第一个解决方案：

Second solution:

第二种解决方案：

采纳答案by Branko Dimitrijevic

回答by Salman A

回答by Jürgen Steinblock

相关推荐

最近更新

标签

MySQL - 一对一的关系？

提问by Limeni

First solution:

第一个解决方案：

Second solution:

第二种解决方案：

采纳答案by Branko Dimitrijevic

回答by Salman A

回答by Jürgen Steinblock

相关推荐

MySQL 如何将文本文件中的数据导入mysql数据库

mysql 喜欢性能提升

MySQL 检查登录详细信息（用户名和密码）的最佳查询

复制 /var/lib/mysql 是 mysqldump 的一个很好的替代品吗？

相关推荐

最近更新

标签