Как я могу удалить все триггеры в базе данных MySQL, используя один оператор SQL?

Ответ 1

Это старый вопрос, но поскольку он тот, который продолжает появляться в моих поисках, я думал, что отправлю решение здесь. В моем случае мне нужно было создать один файл с полным 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 \1;/' >> dump.sql

Чтобы фактически удалить все триггеры в той же команде (как упоминалось в комментариях @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 \1;/' | mysql -u [db user] -p[db password] [db name]

Ответ 2

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

Скопируйте и вставьте сгенерированный sql

Если вам нужно отбросить все триггеры из всех баз данных, обязательно отфильтруйте схему mysql.

SELECT Concat('DROP TRIGGER ', Trigger_Name, ';'), TRIGGER_NAME, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE FROM  information_schema.TRIGGERS WHERE EVENT_OBJECT_SCHEMA <> 'mysql';

Ответ 3

К сожалению, это невозможно в регулярной инструкции SQL или хранимой процедуре.

Решение

Простейшим решением для удаления всех триггеров может быть bash script:

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

Для этого требуется файл учетных данных (.mysql.conf):

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

Рассуждение

Попытка сбросить триггер из хранимой процедуры завершится с ошибкой. Я предполагаю, что это происходит потому, что переменная может содержать только строку, а 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();

На форумах MySQL есть два потока:

• Оператор Drop в хранимой процедуре
• Как удалить триггер в процедуре хранения?

Они оба придерживаются того же вывода, что даже использование хранимой процедуры не поможет.

Ответ 4

Невозможно удалить все триггеры в базе данных MySQL, используя один оператор SQL.

Но со следующим кодом 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';

Результат предыдущего запроса приведет к следующему:

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

И вы можете использовать эти инструкции SQL для окончательного удаления всех триггеров.

Системная переменная сервера group_concat_max_len - максимально допустимая длина результата в байтах для функции GROUP_CONCAT(). Поскольку по умолчанию 1024, нам нужно увеличить его, чтобы избежать усеченного результата, если у нас много триггеров. Я просто использовал значение системной переменной сервера max_allowed_packet, но обычно это нормально любое большое целое число. См. этот другой вопрос.

Ответ 5

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

копировать результаты в буфер обмена

преобразуйте Trigger_name в DROP TRIGGER <trigger_name>; для каждого триггера, используя теги ^ и S для начала и конца строки с рег.выражениями

выполняется как sql script

Ответ 6

Не уверен, что для этой цели существует единственная команда.

Но вот как я сделал это некоторое время назад

• Напишите script, чтобы получить список TRIGGERS в db.
• создать сокращенный список в файле dropTrigger.sql
• Запустить файл dropTrigger.SQL

Чтобы получить список триггеров, вы можете использовать SHOW trigger или Триггеры таблица в информационной схеме

Ответ 7

MAC с использованием MAMP:

Есть некоторые небольшие настройки, которые нужно сделать для принятого ответа для нас. Сначала нам нужно ориентировать mysql в MAMP. Во-вторых, sed -r не работает для Mac, вместо этого используйте sed -E. Предполагая стандартную настройку MAMP, без изменений в настройках пользователя root, просто замените [db name] на свой db, затем скопируйте и вставьте в Terminal и нажмите 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 \1;/' | /Applications/MAMP/Library/bin/mysql -u root -proot [db name]

(если вы обновили свой пароль root, просто замените "root" после -p своим паролем

Ответ 8

Если вы хотите удалить их с помощью запроса без необходимости sqldump базы данных в файл:

select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_schema'; Затем вам нужно будет экспортировать результаты (я считаю, что CSV проще всего) и запускать запросы в этих результатах.

Ответ 9

Быстрый ответ "нет".

Но вы можете инкапсулировать логику в одну хранимую процедуру: получить все триггеры и удалить все из них в цикле с помощью динамического SQL.

После этого вы можете выполнить: call delete_all_triggers_api(schema_name);