如何使用一条 SQL 语句删除 MySQL 数据库中的所有触发器？

How can I delete all the triggers in a MySQL database using one SQL statement?

提问by Matt Gibson

I have a DB that I have cloned, and now all the logging triggers point to log tables in the original schema. I need to delete all of them in one go (there are several dozen) so that I can recreate them. How can this be done with one command?

我有一个已克隆的数据库，现在所有日志记录触发器都指向原始模式中的日志表。我需要一次性删除所有这些（有几十个），以便我可以重新创建它们。这怎么能用一个命令来完成？

回答by Irineu Licks Filho

SELECT Concat('DROP TRIGGER ', Trigger_Name, ';') FROM  information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_schema';

Copy and paste the generated sql

复制粘贴生成的sql

回答by Justin Warkentin

This is an old question, but since it's the one that keeps popping up in my searches I thought I'd post a solution here. In my case I needed to create a single file that had a full mysqldump, would then drop any triggers, and then re-add them all. I was able to do it by using the following command to append the DROP TRIGGERstatements to my dump prior to appending the dump of the triggers.

这是一个老问题，但由于它是我搜索中不断出现的问题，我想我会在这里发布解决方案。在我的情况下，我需要创建一个具有完整的文件，mysqldump然后删除所有触发器，然后重新添加它们。DROP TRIGGER在附加触发器的转储之前，我能够通过使用以下命令将语句附加到我的转储中来做到这一点。

mysql -u [db user] -p[db password] --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS ;/' >> dump.sql

To actually drop all the triggers in the same command (as mentioned by @Stephen Crosby in the comments), you can just pipe this back into MySQL like so:

要实际删除同一命令中的所有触发器（如@Stephen Crosby 在评论中所述），您可以像这样将其管道回 MySQL：

mysql -u [db user] -p[db password] --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS ;/' | mysql -u [db user] -p[db password] [db name]

回答by Ian Mackinnon

Unfortunately this is not possible in a regular SQL statement or a stored procedure.

不幸的是，这在常规 SQL 语句或存储过程中是不可能的。

Solution

解决方案

The simplest solution to drop all triggers might be a bash script:

删除所有触发器的最简单解决方案可能是 bash 脚本：

echo "select concat('drop trigger ', trigger_name, ';')
  from information_schema.triggers where trigger_schema = 'your_database'" |
  mysql --defaults-extra-file=.mysql.conf --disable-column-names | 
  mysql --defaults-extra-file=.mysql.conf

This requires a credentials file (.mysql.conf) like so:

这需要一个凭据文件 ( .mysql.conf)，如下所示：

[client]
database=your_database
user=your_username
password=your_password

Reasoning

推理

Attempting to drop a trigger from a stored procedure will fail. I'm guessing this happens because a variable can only hold a string and drop triggerrequires a trigger name (not a string containing a trigger name):

尝试从存储过程中删除触发器将失败。我猜这是因为一个变量只能保存一个字符串并且drop trigger需要一个触发器名称（不是一个包含触发器名称的字符串）：

create procedure drop_a_trigger()
begin
  declare var1 varchar(1024);
  set var1 = "my_second_trigger";

  drop trigger my_first_trigger;       // OK
  drop trigger address_update_before;  // ERROR 1360 (HY000): Trigger does not exist

end //
delimiter ;

call drop_a_trigger();

There are two threads about this on the MySQL forums:

MySQL 论坛上有两个关于此的主题：

• Drop statement in stored procedure
• How to drop trigger in store procedure?

• 存储过程中的drop语句
• 如何在存储过程中删除触发器？

They both reach the same conclusion that even using a stored procedure will not help.

他们都得出相同的结论，即即使使用存储过程也无济于事。

回答by cgaldiolo

It is not possible to delete all the triggers in a MySQL database using one SQL statement.

使用一条 SQL 语句删除 MySQL 数据库中的所有触发器是不可能的。

But with the following SQL code you can build the list of all the 'DROP TRIGGER' statements (replace <your_schema>with your schema name, e.g. your database name):

但是使用以下 SQL 代码，您可以构建所有“DROP TRIGGER”语句的列表（将<your_schema>替换为您的架构名称，例如您的数据库名称）：

-- set `group_concat_max_len`
SET @@session.group_concat_max_len = @@global.max_allowed_packet;

-- select all the triggers and build the `DROP TRIGGER` SQL
-- replace <your_schema> with your schema name (e.g. your database name)
SELECT GROUP_CONCAT(sql_string SEPARATOR '\n')
FROM (
    SELECT CONCAT('DROP TRIGGER IF EXISTS `', TRIGGER_NAME, '`;') AS sql_string,'1'
    FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = '<your_schema>'
    ) AS sql_strings
GROUP BY '1';

The result of the previous query will produce something like:

上一个查询的结果将产生如下结果：

DROP TRIGGER IF EXISTS `trigger1`;
DROP TRIGGER IF EXISTS `trigger2`;
(...)

And you can use these SQL statements to finally delete all the triggers.

并且您可以使用这些 SQL 语句最终删除所有触发器。

The server system variable group_concat_max_lenis the maximum permitted result length in bytes for the GROUP_CONCAT() function. Because the default is 1024, we need to increase it to avoid a truncated result if we have a lot of triggers. I just used the value of the server system variable max_allowed_packet, but usually is fine any big integer number. See this other question.

服务器系统变量group_concat_max_len是 GROUP_CONCAT() 函数允许的最大结果长度（以字节为单位）。因为默认是1024，如果我们有很多触发器，我们需要增加它以避免截断结果。我只是使用了服务器系统变量max_allowed_pa​​cket的值，但通常任何大整数都可以。看到这个其他问题。

回答by user3155977

SELECT Trigger_Name
FROM `information_schema`.`TRIGGERS`
WHERE TRIGGER_SCHEMA = 'your_schema';

copy results to clipboard

将结果复制到剪贴板

convert Trigger_nameto DROP TRIGGER <trigger_name>;for each trigger using ^ and S tags for begin and end of line with reg.expressions

转换Trigger_name到DROP TRIGGER <trigger_name>;了开始和reg.expressions行结束使用每个触发器^和S标签

run as sql script

作为 sql 脚本运行

回答by webaholik

MAC using MAMP:

MAC 使用 MAMP：

There's some small tweaks that need to be done to the accepted answer for us. First we need to target mysql in MAMP. Second, sed -rdoesn't work for Mac, use sed -Einstead. Assuming the standard MAMP setup, with no changes to root user defaults, just replace [db name] with your db, then copy and paste into Terminal and hit enter.

需要对我们接受的答案进行一些小的调整。首先，我们需要在 MAMP 中以 mysql 为目标。其次，sed -r不适用于 Mac，请sed -E改用。假设标准 MAMP 设置，没有更改 root 用户默认值，只需将 [db name] 替换为您的 db，然后复制并粘贴到终端并按 Enter。

/Applications/MAMP/Library/bin/mysql -u root -proot --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -E 's/(.*)/DROP TRIGGER IF EXISTS ;/' | /Applications/MAMP/Library/bin/mysql -u root -proot [db name]

(if you have updated your root password, just swap out "root" after -pwith your password

（如果你更新了你的 root 密码，只需-p用你的密码换出“root”

回答by metalfight - user868766

Not sure about existence of single command for this purpose.

不确定是否存在用于此目的的单个命令。

But here is how i did it some time back

但这是我前一段时间做的

1. Write script to get list of TRIGGERS in db.
2. create Concated list in dropTrigger.sql file
3. Run dropTrigger.SQL file

1. 编写脚本以获取数据库中的触发器列表。
2. 在 dropTrigger.sql 文件中创建连接列表
3. 运行 dropTrigger.SQL 文件

To get List of triggers you can either use SHOW triggeror Triggers table in Information schema

要获取触发器列表，您可以在信息架构中使用SHOW trigger或 Triggers 表

回答by Naguib Ihab

In case you want to drop them using a query without the need to sqldump the database into a file:

如果您想使用查询删除它们而无需将数据库 sqldump 到文件中：

select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_schema';You will then need to export the results (I find to CSV is easiest) and run the queries in those results.

select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_schema';然后您需要导出结果（我发现 CSV 是最简单的）并在这些结果中运行查询。

回答by ravnur

The quick answer is "no".

快速回答是“不”。

But you can encapsulate logic in one stored procedure: get all triggers and delete all of them in loop using dynamic SQL.

但是您可以将逻辑封装在一个存储过程中：使用动态 SQL 在循环中获取所有触发器并删除所有触发器。

After is you can execute: call delete_all_triggers_api(schema_name);

之后你可以执行： call delete_all_triggers_api(schema_name);