我想在给定的一组表上为DELETE事件构建触发器,如下所示:
foo
foo_refs
bar
bar_refs
以 _refs 结尾的表的表架构如下:
reference_source VARCHAR(175) NOT NULL COLLATE utf8mb4_unicode_ci,reference_source_id BIGINT(64) UNSIGNED
其背后的想法是将表与参考系统相互关联。假设我想在 foo 的 bar 中添加对第15行的引用。我将在 foo_refs 中添加新行,在reference_source中写入bar
,在reference_source_id中写入15
。到目前为止,一切都很好。
即使这不是我想要的最佳解决方案(以防我从 foo 删除一行)遍历所有以 _refs 结尾的表,对于每个表,删除任何指向我要删除的表格行的行。为此,我构建了此示例触发器(我知道它是不正确的,但稍后会介绍更多信息):
CREATE TRIGGER foo_evnt_delt AFTER DELETE ON foo FOR EACH ROW
BEGIN
WITH matches AS (
SELECT
DISTINCT(table_name) AS table_name
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name <> 'foo_refs'
AND table_name LIKE '%_refs'
) DELETE FROM matches.table_name
WHERE reference_source = 'foo'
AND reference_source_id = OLD.foo_id;
END;
正如我之前所说,我知道这本身是不正确的……至少在这里有两个问题:1.)我不确定DELETE
语句是否适用,因为我假设从WITH
子句中进行选择会把表名交给我,或者我以后需要选择它们(我对WITH
子句的经验很少)。 2.)如果第一个假设是正确的,我还没有找到MySQL可以接受table_name字段作为表名的方法(老实说我认为不是)。
即使尝试类似SELECT * FROM concat('','foo')
之类的方法也不起作用(我已经读过很多关于eval()的关于MySQL字符串的SO帖子,这不是我想做的事情。)
我可以使用PHP而不是MySQL轻松解决此问题,因为我的数据库类中已经有方法可以列出当前模式中的表,甚至可以自动构建数据模型,我只需要构建一个方法来处理这种情况,但是如果可能的话,我希望MySQL承担繁重的任务。
您能给我一两个提示,以了解如何在触发器中正确执行此操作吗?