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,因此这种关系一对一。
不,这使得关系“一到零或一”。 那是你真正需要的吗?
如果是 ,那么你的“第二个解决方案”更好:
- 它更简单,
- 占用较少的存储1 (因此使缓存“更大”)
- 他维持2的索引更少,这有利于数据操作,
- (因为你使用InnoDB)自然地clusters数据,因此靠近的用户也会将他们的帐户紧密地存储在一起,这可能有利于缓存局部性和某些范围的扫描。
顺便说一下,你需要创建一个account.id一个普通的整数(不是自动增量)才能生效。
如果不是 ,请参阅下文......
在MySQL中创建一对一关系的最佳方法是什么?
好吧,“最好”是一个重载词,但“标准”解决方案与任何其他数据库中的相同:将两个实体(用户和帐户在你的情况下)放在同一个物理表中。
除了这两个之外还有其他解决方案吗?
从理论上讲,你可以在两个PK之间制作循环FK,但这需要延迟约束来解决鸡与蛋的问题,遗憾的是MySQL不支持这个问题。
如果我将这些解决方案中的任何一个导入到MySQL Workbench EER图中,它会将关系视为一对多:这也是令人困惑的。
我对这个特定的建模工具没有太多的实际经验,但我猜这是因为它是“一对多”,其中“很多”方面通过使其独特而被限制在1。 请记住,“很多”并不意味着“1或许多”,它意味着“0或许多”,因此“上限”版本的确意味着“0或1”。
1不仅在附加字段的存储费用中,而且在二级索引中也是如此。 由于您使用的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首先删除映射。