MySQL - 一对一关系？

Question

MySQL - 一对一关系？

mysqlsqldatabasedatabase-designone-to-one

69

我正在尝试在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;

在这个例子中，我将accounts表中的外键定义为指向users表中的主键。然后我使外键成为唯一的，这样在accounts中就不会有两个相同的用户。要连接这两个表，我会使用以下查询：

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中定义一对一关系的最佳方法是什么？还有哪些选项可以实现此目的？

- Limeni

6

要明确的是，如果您在数据库中建立1：1关系，真的应该问自己是否没有必要自找麻烦。我很少遇到不将所有值放入单个表格比建立1：1关系更好的情况。 - AmericanUmlaut

你提到了“我确实知道有一些关于一对一关系的答案”。如果你能链接到那些问题中的某一个，那么如果其他人在网上找到他们的解决方案，那就太好了。 - santiago arizti

2

@AmericanUmlaut，一个原因是适当的规范化。两个不同的实体不应该放在一个表中（比如用户和人员）。另一个原因是为了防止表水平增长过多，因为这会显著影响性能。 - AbiusX

3个回答

10

你的第一个方法在账户表中创建了两个候选键：id 和 user_id。

因此，我建议采用第二种方法，即使用外键作为主键。这样做有以下好处：

使用一列更少
允许你唯一地标识每行
允许你匹配账户和用户

- Salman A

1

谢谢！你知道为什么MySQL Workbench EER图将这个关系视为一对多吗？:S - Limeni

1

找到了MySQL Workbench的解决方案：http://bugs.mysql.com/bug.php?id=43920 有趣的事情，这不是一个错误... - Limeni

4

我觉得使用 ID 作为外键可能会有麻烦。因为数据库会自动生成这些 ID，你无法保证两个 ID 是完全相同的（理论上它们应该是一样的，但你不能假设）。事实上，由于你的示例在账户->用户关系中没有定义 NOT NULL，我认为有可能存在账户没有用户或者反过来的情况。如果你创建了多个用户，然后以不同的顺序创建他们的账户，那么你的关系就会断掉。 - AmericanUmlaut

2

我认为这是更清晰的解决方案。实用角度来看，如果你总是同时创建和删除两张表，并使用ID，那么很可能永远不会出现问题，但如果你碰巧遇到了一些边缘情况，导致ID不同步，那就会陷入麻烦之中。 - AmericanUmlaut

@AmericanUmlaut：为什么你想要“自动生成”一个外键？ - Salman A

显示剩余2条评论

-2

以下方法怎么样？

创建用户表

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和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` (
  `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;

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

将FK_Key放在用户表或账户表中是我预期的一对多关系（用户有多个账户...）
虽然这种user2account方法主要用于定义多对多关系，但在user_id和account_id上添加唯一约束将防止创建除一对一关系以外的其他关系。

我认为这种解决方案的主要优点是您可以将工作分成不同的代码层或公司部门

部门A负责创建用户，即使没有对账户表的写入权限也可以实现
部门B负责创建账户，即使没有对用户表的写入权限也可以实现
部门C负责创建映射，即使没有对用户或账户表的写入权限也可以实现
一旦部门C创建了映射，部门A或B不能在未经部门C允许删除映射的情况下删除用户或账户。

- Jürgen Steinblock

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Branko Dimitrijevic · Accepted Answer

由于主键默认为唯一，因此这使得该关系为一对一关系。但是，这实际上是“一对零或一”的关系。如果是您所需的，那么您的“第二种解决方案”更好：它更简单，占用的存储空间更少（因此使缓存“更大”），需要维护的索引更少，这有利于数据操作，并且（由于您正在使用InnoDB）自然地聚集数据，因此彼此接近的用户的帐户也会被存储在靠近一起的位置，这可能有助于缓存局部性和某些范围扫描。顺便说一句，为使其正常工作，您需要将accounts.id设置为普通整数（而不是自动递增）。如果不是，则请参见下文... “在MySQL中创建一对一关系的最佳方法是什么？”

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

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

从理论上讲，您可以在两个PK之间建立循环FK，但这需要“延迟”约束来解决鸡生蛋问题，而MySQL不支持这些约束。

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

我没有使用过那个建模工具，但我猜这是因为它是“一对多”，其中“多”侧被限制为1，使其成为唯一的。请记住，“多”并不意味着“1或多个”，它意味着“0或多个”，因此“被限制”的版本实际上意味着“0或1”。

¹ 不仅是因为额外字段的存储费用，还因为二级索引的费用。而且，由于您使用的是InnoDB，它总是将表聚集在一起，请注意，在聚集表中，辅助索引比堆表中更加昂贵。

² InnoDB 需要对外键建立索引。