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

Question

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?

Answer 1

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

Copy and paste the generated sql

Answer 2

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 TRIGGER statements to my dump prior to appending the dump of the triggers.

mysql -u [db user] -p[db password] --skip-column-names [db name] -e 'SHOW TRIGGERS;' | cut -f1 | sed -r 's/(.*)/DROP TRIGGER IF EXISTS \1;/' >> 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:

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