MySQL 中 0..1:1..n 表的 SQL 自动清理

Question

我正在编写一个应用程序，要求所有用户使用 MySQL 访问中央数据库上的数据，我想知道一些事情。

假设我有这个设置。

CREATE TABLE A
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    data INT NOT NULL;
);

CREATE TABLE B
(
   id    INT PRIMARY KEY AUTO_INCREMENT,
   a_id  INT,
   FOREIGN KEY (a_id) REFERENCES A(id) ON DELETE SET NULL
);

现在，我希望这种设置的方式是，表 A 必须始终由表 B 中的行引用。但是，表 B 中的行可能会也可能不会引用表 A 中的行。关系是 1:n表 B 中的多行可以引用表 A 中的单行。我只是想知道如果 A 中的行不再被表 B 中的任何行引用，是否可以让 MySQL 数据库自动删除该行。

这里的想法是我可以简单地在表中设置 a_id B 为 NULL 并让数据库清理剩下的任何内容。现在想起来，我想这与 Java 垃圾收集类似。如果没有自动执行约束的键，更新后执行的触发器会起作用吗？

编辑：添加附加关系约束。

Answer 1

以特定时间间隔运行以下查询：

DELETE tableA
FROM tableA LEFT JOIN tableB B ON A.id = B.a_id
WHERE B.a_id IS NULL;

或者，为了保持实时一致性，您可以在 tableB 上创建执行类似操作的 OnChange 触发器。

Answer 2

您使用的表结构有什么原因吗？您使用外键的方式在我看来是倒退的。您可以将密钥移至表 A，而不是将其放在表 B 中。

这会给您一个看起来更像是的结构：

tableA columns        tableB columns
  id
  b_id                id
  [values]            [values]

  fk: a.b_id=b.id

添加到表 A 的记录需要表 B 中的相应字段，但 B 可以自由地拥有表 A 中没有值。那么如果您想清除表 A 中的值，您可以简单地使用：

delete from tableA where b_id=[recordIdToNull];

您甚至可以将 A 的外键设置为对 B 执行的级联操作，因此从 B 中删除的任何值也会删除相应的值A 中的行。