MySQL-一对一的关系?[英] MySQL - One To One Relation?

本文是小编为大家收集整理的关于MySQL-一对一的关系?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

现在我知道有关一对一关系的一些答案,但他们都没有回答我的问题,所以请在下面的投票前阅读:)



Im尝试在MySQL数据库中实现一对一关系.例如让我说我有Users表和Accounts Table.我想确保有用户只能有一个帐户.并且每个用户只能有一个帐户.



我发现了两个解决方案,但不知道要使用什么,还有任何其他选项.



第一个解决方案:



  DROP DATABASE IF EXISTS test; 
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
使用测试;

CREATE TABLE用户(
id INT NOT NULL AUTO_INCREMENT,
user_name VARCHAR(45)NOT NULL,
PRIMARY KEY(id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;

CREATE TABLE帐户(
id INT NOT NULL AUTO_INCREMENT,
account_name VARCHAR(45)NOT NULL,
user_id INT UNIQUE,
PRIMARY KEY ),
FOREIGN KEY(user_id)REFERENCES users(id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;


在本例中,我们在指向用户主键的帐户中定义外键.
然后我使外键UNIQUE,所以帐户中不能有两个相同的用户.
要连接表,我将使用这个查询:



  SELECT * FROM users JOIN帐户ON users.id = accounts.用户名; 


第二个解决方案:



  DROP DATABASE IF EXISTS test; 
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
使用测试;

CREATE TABLE用户(
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帐户ON users.id = accounts.id ; 


现在的问题:




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

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

  • < ul>

    我使用MySQL Workbench,当我在EER图中设计一对一关系,并让MySQL Workbench生成SQL代码时,我得到一对多关系:S这是什么令人困惑的是:S



    如果我将任何这些解决方案导入到MySQL Workbench EER图中,它将关系识别为一对多:

    因此,在MySQL DDL中定义一对一关系的最好方法是什么.


    推荐答案



    blockquote>

    由于主键在默认情况下是唯一的,这使得这种关系一对一.




    使得"一个为零或一个"的关系.



    如果是,那么您的"第二个解决方案"会更好:




    • 更简单,

    • 所需的存储空间少于 1 li>
    • 更少的索引来维护 2 ,这有利于数据处理,

    • href ="http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index ="noreferrer">集群数据,因此靠近在一起的用户将他们的帐户也存储在一起,这可能有利于缓存位置和某些类型的范围扫描.



    使 accounts.id 一个普通整数(不是自动递增),以便此工作.



    如果 ,请参阅下文...




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



    好的,"best"是一个重载的单词,但"标准"解决方案将与任何其他数据库中的相同:




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




    理论上,您可以在两个PK之间建立循环FK,但这需要延迟约束来解决鸡肉和




    如果我将任何这些解决方案导入到MySQL Workbench EER图表中,关系作为一对多:S这也是困惑.




    我没有太多的实际经验,那个特定的建模工具,猜测这是因为它是"一对多",其中"许多"一方被限制在1,使其独特.请记住,"许多"并不意味着"1或许多",它意味着"0或许多",因此"封顶"版本真的意味着"0或1".






    1 不仅仅是额外字段的存储费用,而且是辅助索引.由于您使用的是总是群集表的InnoDB,因此请注意,



    2 InnoDB 需要外键上的索引.


    本文地址:https://www.itbaoku.cn/post/597524.html

问题描述

Now I do know there are some answers about One To One Relation, but neither of them answered my question, so please read this before down vote :)

Im trying to achive One To One relation in MySQL database. For example lets 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.

I found two solutions for this but dont 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 foreign key in accounts pointing to primary key in users. And then I make foreign key UNIQUE, so there cant 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 foreign key that points from 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?

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

And if I import any of these solutions into MySQL Workbench EER diagram, it recognizes relations as One to Many :S Thats also confusing.

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

Thanks!!

推荐答案

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 storage1 (and therefore makes cache "larger")
  • hes less indexes to maintain2, which benefits data manipulation,
  • and (since you are using InnoDB) naturally clusters the 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.

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

If no, see below...

What is the best way to create One to One relation in 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 deferred constraints to resolve the chicken-and-egg problem, which are unfortunately not supported under 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.

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

2 InnoDB requires indexes on foreign keys.