MySQL 禁用所有触发器 [英] MySQL disable all triggers

问题描述

为了测试查询的正确性，我需要禁用数据库中的所有触发器.我看到在 information_schema 中存在表 TRIGGERS.是否可以使用此表暂时禁用所有触发器?例如.喜欢:

For test correctness of query I need disable all triggers in db. I see that in information_schema exists table TRIGGERS. Is possible temporarily disable all triggers using this table? E.g. like:

update TRIGGERS set TRIGGERS_SCHEMA='myschema_new' 
where TRIGGERS_SCHEMA='myschema'

并在完成所有测试后返回所有触发器，例如:

and after finish all test return all triggers like:

update TRIGGERS set TRIGGERS_SCHEMA='myschema'
where TRIGGERS_SCHEMA='myschema_new'

这可能会损坏数据库还是触发器不起作用?我没有在文档中找到它.

May be this can corrupt db or after triggers will not works? I didn't found about it in documentation.

推荐答案

你不能直接禁用触发器，我不建议你按照你的建议去做，但你可以让触发器检查变量(在我的例子中)在执行触发器的内容之前，@disable_triggers) 是 NULL.例如:

You can't disable triggers directly and I wouldn't recommend doing what you're suggesting but you could have your trigger check if a variable (in my example below @disable_triggers) is NULL before executing the trigger's content. For example:

查询:

SET @disable_triggers = 1;
// Your update statement goes here.
SET @disable_triggers = NULL;

触发器:

IF @disable_triggers IS NULL THEN
    // Do something use as the trigger isn't disabled.
END IF;

这篇关于MySQL 禁用所有触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！