MySQL数据库设计。向一对一表中插入行。

Question

什么是将具有1对1引用关系的行插入表中的最佳方法？

我的意思是，在MySQL 5.5和InnoDB表中，我有一个类似于以下设计的数据库 当我们尝试在table1和table2中插入行时，问题就出现了。由于MySQL中没有多表插入，因此我无法插入一行，因为外键是两个表中的NOT NULL字段，并且应同时插入两个字段。

哪种方法是解决此问题的最佳方式？

我考虑了3种可能的解决方案，但我想知道是否还有更多解决方案，或者哪种方法最好以及原因。

1.将外键字段设置为NULLABLE，然后在一个表中插入一行后，在另一个表中插入另一行，之后更新第一个表。 2.就像上面所示，但使用特殊值（如-1）。首先，在具有foreign key = -1的一个表中插入，这相当于NULL，但避免将该字段设置为NULLABLE。之后，在另一张表中插入行并更新第一个插入的行。 3.创建一个在两者之间的关系表，尽管这不是真正必要的，因为它是1对1的比率

谢谢！

编辑： 我简要解释了我需要这种循环关系的原因：它是从父表到其一个子表的去规范化。为了高性能，它始终具有来自父表的最佳排名子表的引用。

- Emilio Nicolás

---

我经常观察到这种关系的情况是两个表格应该合并成一个，或者只需要一个表格引用另一个表格。 - Jacob

在这种情况下，那是不正确的。无论如何，谢谢！ - Emilio Nicolás

@Emilio：你可能认为在这种情况下不正确。但也许有一种方法可以改变这两个表的设计，使得没有像这样的循环路径。你能描述一下为什么需要这两个关系吗？这两个表是用来做什么的？ - ypercubeᵀᴹ

@Emilio：这是一个常见的设计缺陷。如果两个表处于1:1关系，为什么不只有一个表呢？ - ypercubeᵀᴹ

这是从父表到其子表之一的反规范化。为了高性能，始终具有父表中最佳排名子项的引用而进行。 - Emilio Nicolás

Answer 1

我会把这个作为答案，因为我认为这是一个设计缺陷。

首先，如果两个表之间是真正的1:1关系，为什么不只有一个表呢？

其次，如果不是真正的1:1关系而是超类型-子类型问题，你也不需要这些循环外键。假设table1是Employee，table2是Customer。当然，大多数客户不是员工（反之亦然）。但有时候一个顾客也可能是一名员工。这可以通过拥有3个表来解决：

Person
------
id
PRIMARY KEY: id

Employee
--------
personid
lastname
firstname
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

Customer
--------
personid
creditCardNumber
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

在您描述的场景中，您有两个表Parent和Child，它们之间具有1:N的关系。然后，您想以某种方式存储每个Parent对应的最佳Child（基于定义的计算结果）。

这样做可以吗？

Parent
------
id
PRIMARY KEY: id

Child
-----
id
parentid
... other data
PRIMARY KEY: id
FOREIGN KEY: parentid
    REFERENCES Parent(id)
UNIQUE KEY: (id, parentid)             --- needed for the FK below

BestChild
---------
parentid
childid
... other data
PRIMARY KEY: parentid
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)

这样做可以实现所需的引用完整性（每个BestChild都是Child，每个Parent只有一个BestChild），并且在References中没有循环路径。最佳子项的引用存储在额外的表中，而不是Parent表中。

您可以通过连接找到每个Parent的BestChild：

Parent
  JOIN BestChild
    ON Parent.id = BestChild.parentid
  JOIN Child
    ON BestChild.childid = Child.id

此外，如果您想为多个性能测试（针对不同类型的测试或在不同日期进行的测试）存储最佳子代，则可以添加一个test字段，并将主键更改为(test, parentid):

BestChild
---------
testid
parentid
childid
... other data
PRIMARY KEY: (testid, parentid)
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)
FOREIGN KEY: testid
    REFERENCES Test(id)

- ypercubeᵀᴹ

---

谢谢您的回答！我已经编辑了问题，以澄清需要这种关系的原因。 - Emilio Nicolás

@Emilio：也要考虑这个选项。没有触发器，也没有循环引用，额外的表已经标准化。（如果您希望每次更改子级属性时更新性能最佳的子级，则可以使用触发器）。 - ypercubeᵀᴹ

Answer 2

我会创建一个黑洞表，并在其上放置触发器来处理插入操作。

CREATE TABLE bh_table12 (
  table1col varchar(45) not null,
  table2col varchar(45) not null
) ENGINE = BLACKHOLE

并在其上设置触发器以处理插入操作

DELIMITER $$

CREATE TRIGGER ai_bh_table12_each AFTER INSERT ON bh_table12 FOR EACH ROW
BEGIN
  DECLARE mytable1id integer;
  DECLARE mytable2id integer;

  SET foreign_key_checks = 0;
    INSERT INTO table1 (table1col, table2_id) VALUES (new.table1col, 0);
    SELECT last_insert_id() INTO mytable1id;
    INSERT INTO table2 (table2col, table1_id) VALUES (new.table2col, table1id);
    SELECT last_insert_id() INTO mytable2id;
    UPDATE table1 SET table2_id = mytable2id WHERE table1.id = mytable1id;
  SET foreign_key_checks = 1;
END $$

DELIMITER ;

请注意，触发器中的操作是一个事务的一部分（使用InnoDB或类似引擎），因此如果触发器出现错误，将回滚部分更改。

关于您的表结构的说明
请注意，如果它是一个1对1的表格，您只需要在table1中放置table2_id，而不需要在table2中放置table1_id（反之亦然）。
如果您需要根据table2查询table1，您可以使用以下语句：

SELECT table1.* FROM table1
INNER JOIN table2 on (table2.id = table1.table2_id)
WHERE table2.table2col = 'test2'

同样地，对于另一种方式也是如此。

SELECT table2.* FROM table2
INNER JOIN table1 on (table2.id = table1.table2_id)
WHERE table1.table1col = 'test1'

Links:
http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/triggers.html

- Johan

---

谢谢你的回答！我已经编辑了问题，以澄清为什么需要这种关系。然而，我不知道黑洞引擎，你能否请详细解释一下。 再次感谢！ - Emilio Nicolás

blackhole 引擎是一个数据库存储，它会忘记你放入其中的所有内容，你可以把任何东西放进去，但却永远无法取出（就像 Linux 上的 /dev/null）。这非常有用，因为你可以附加日志文件和触发器到黑洞上，让它执行操作而无需进行清理。 - Johan

Answer 3

我认为这是一个重要的问题，在网上我没有找到任何100%令人满意的答案。你给出的两个答案是我找到的最好的答案，但它们并不完全令人满意。

原因如下：

• Emilio不能将他最好的孩子放在父表中的原因非常简单，我想，因为我有同样的问题：并不是每个孩子都会被标记为父母的最好的孩子。所以他仍然需要在其他地方存储其他孩子的信息。在这种情况下，他将在父表中拥有一些关于最好的孩子的信息，并在另一个数据库中拥有其他孩子的信息。这是一团糟。例如，当他想要更改有关孩子的数据结构的信息时，他需要在两个表中进行更改。每次他查询所有孩子的查询时，他都应该查询两个表等等...

• Emilio不能只将最好的孩子外键设置为可空（我想对于Emilio来说是这样，但对于我来说则非常严格），原因是他需要确保父母始终拥有最好的孩子。在Emilio的情况下，这可能不太容易想象，但在我的情况下，我不能让父母没有孩子的等价物。

因此，我本来倾向于认为将foreign_key_checks设置为零的解决方案是最好的，但问题在于：

• 在将foreign_key_checks重新设置为1之后，数据的一致性没有进行检查。因此，在此期间犯错的风险很大。你可以认为自己不会犯错，但这仍然不是一个非常干净的解决方案。

- Vic Seedoubleyew