mysql - 数据表和查表同时级联删除

Question

我正在存储目录和文件的文件系统层次结构。

在 innodb 表中，我存储了每个目录/文件的详细信息，并使用将在删除时级联的外键约束维护父子关系。

myisam 表用于通过全文搜索来搜索这些目录/文件。它包含每一行的名称和 ID。

数据表(innodb 表)中的任何行在搜索表(myisam 表)中都有相应的行，并且从数据表中添加或删除行必须反射(reflect)在搜索表中。

我正在尝试找到在删除父目录时保持两个表之间数据一致性的最佳解决方案。 innodb 表很好。我删除父级，删除级联通过子级，直到它们全部被删除。从 myisam 表中删除相应的行更加困难。

我的第一个想法是在 innodb 表上使用删除触发器。当删除一行时，它会从 myisam 表中删除相应的行。但是，由于 MySQL 在级联删除期间不激活触发器(7 年来已知的错误，通过在手册中提到缺乏支持而得到修复)，这不是一个选项。

我的第二个想法是在搜索表中放置一个父子关系，但是它是一个支持全文搜索功能的myisam表，所以它不支持外键约束。

我听说 innodb 现在支持全文搜索，所以我想也许我可以更改搜索表引擎，但它只在实验室版本中可用。

我最后的想法是放弃外键约束，只使用触发器来保持数据的一致性。删除时，从 innodb 和 myisam 表中删除 parent = OLD.id。但是，为了防止无限循环破坏表中的所有数据，MySQL 不支持在激活触发器的同一个表中操作数据。

我已经求助于通过请求循环以编程方式检索父目录下的所有子目录，但是，我觉得必须有更好的选择。 还有其他更有效的解决方法吗？在这一点上，我能想到的唯一两个选择是等待上述方法之一得到修复或更改为不同的 RDBMS，如 PostgreSQL确实支持从级联删除中触发触发器。

任何其他想法将不胜感激。

Answer 1

这些令人头疼的事情正是让我尽可能远离 mysql 的原因。

... I feel there has got to be a better option ...

遗憾的是没有。简单的问题是你不能删除 cascade 并且让 mysql 知道它刚刚删除了什么。因此，您唯一的选择是在它删除之前找出它要删除的内容(这是您最后建议的算法)。

由于级联会破坏您的数据，因此您不应使用 on update cascade 键，这样尝试删除父目录而不删除子目录将会失败。

我建议您创建一个程序来为您完成繁重的工作(删除)。这将防止您的应用程序和数据库之间出现大量 IO，因为它会遍历所有目录。如果您曾经通过不同的应用程序访问同一个数据库(或者您只想手动执行某些操作)，Iy 还将提供这样做的通用代码。

正如我首先提到的，这些天我主要使用 postgresql。这是原因之一。