mysql - 关联表 - 数据库多对多中间表

MySQL-一对一的关系? (2)

现在我知道有一对一关系的答案，但他们都没有回答我的问题，所以请在下来之前阅读这个:)

我试图在MySQL数据库中实现一对一的关系。例如，假设我有Users表和Accounts Table。我想确定用户只能拥有一个帐户。并且每个用户只能有一个帐户。

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

第一解决方案

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;

在此示例中，我在指向用户主键的帐户中定义外键。然后我将外键设为UNIQUE，因此帐户中不能有两个相同的用户。要连接表，我会使用此查询：

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，因此这种关系一对一。要连接表，我可以使用：

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

现在的问题是：

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

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

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

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

谢谢！！

由于主键默认为UNIQUE，因此这种关系一对一。

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

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

它更简单，
占用较少的存储¹ （因此使缓存“更大”）
他维持^2的索引更少，这有利于数据操作，
（因为你使用InnoDB）自然地clusters数据，因此靠近的用户也会将他们的帐户紧密地存储在一起，这可能有利于缓存局部性和某些范围的扫描。

顺便说一下，你需要创建一个account.id一个普通的整数（不是自动增量）才能生效。

如果不是，请参阅下文......

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

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

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

从理论上讲，你可以在两个PK之间制作循环FK，但这需要延迟约束来解决鸡与蛋的问题，遗憾的是MySQL不支持这个问题。

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

我对这个特定的建模工具没有太多的实际经验，但我猜这是因为它是“一对多”，其中“很多”方面通过使其独特而被限制在1。请记住，“很多”并不意味着“1或许多”，它意味着“0或许多”，因此“上限”版本的确意味着“0或1”。

¹不仅在附加字段的存储费用中，而且在二级索引中也是如此。 由于您使用的InnoDB 始终聚集表，因此请注意，在集群表中，二级索引比在基于堆的表中更昂贵。

² InnoDB 需要外键索引。

以下方法怎么样？

创建表用户

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;

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

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

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

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