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




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

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

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

如果 ,那么你的“第二个解决方案”更好:

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

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

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

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

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

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

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

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

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

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

2 InnoDB 需要外键索引

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

我试图在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中定义一对一关系的最佳方法是什么。 有什么选择可以实现这个目标?

谢谢!!


以下方法怎么样?

  1. 创建表用户

    CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(45) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
  2. 使用user_idaccount_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;
    
  3. 创建表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_idaccount_id上添加UNIQUE约束将阻止创建除一对一关系之外的其他内容。

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

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




one-to-one