하나의 SQL 문을 사용하여 MySQL 데이터베이스의 모든 트리거를 삭제하려면 어떻게 해야 합니까?

하나의 SQL 문을 사용하여 MySQL 데이터베이스의 모든 트리거를 삭제하려면 어떻게 해야 합니까?

복제한 DB가 있는데, 이제 모든 로깅 트리거가 원래 스키마의 로그 테이블을 가리킵니다.한 번에 모두 삭제해야 다시 만들 수 있습니다(몇 십 개가 있습니다.명령 하나로 어떻게 할 수 있습니까?

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

생성된 sql을 복사하여 붙여넣기

이것은 오래된 질문이지만 검색에 계속 뜨기 때문에 해결책을 여기에 게시해야겠다고 생각했습니다.제 경우에는 전체 파일이 있는 파일 하나를 만들어야 했습니다.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]

그러나 일반 SQL 문이나 저장 프로시저에서는 불가능합니다.

해결책

모든 트리거를 삭제하는 가장 간단한 솔루션은 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

자격 증명 파일이 필요합니다(.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 포럼에는 다음과 같은 두 가지 스레드가 있습니다.

• 저장 프로시저에서 폐기문
• 스토어 절차에서 트리거를 드롭하는 방법?

둘 다 저장된 절차를 사용하는 것조차 도움이 되지 않는다는 같은 결론에 도달합니다.

하나의 SQL 문을 사용하여 MySQL 데이터베이스의 모든 트리거를 삭제할 수는 없습니다.

그러나 다음 SQL 코드를 사용하면 모든 'DROP TRIGER' 문 목록을 만들 수 있습니다(<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 값을 사용했는데 보통 큰 정수라도 괜찮습니다.다른 질문을 참조하십시오.

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

결과를 클립보드에 복사

개종시키다Trigger_name.DROP TRIGGER <trigger_name>;reg.expression으로 시작 및 끝 줄에 대해 ^ 및 S 태그를 사용하여 각 트리거에 대해

sql 스크립트로 실행

이 목적을 위한 단일 명령이 존재하는지 확실하지 않습니다.

하지만 얼마 전에 제가 한 방법은 이렇습니다.

1. 스크립트를 작성하여 db의 트리거 목록을 가져옵니다.
2. dropTrigger.sql 파일에 Concated list 생성
3. dropTrigger(트리거를 실행합니다.SQL 파일

트리거 목록을 가져오려면 정보 스키마에서 SHOW 트리거 또는 트리거 테이블을 사용할 수 있습니다.

MAMP를 사용한 MAC:

우리를 위해 받아들여진 답변에 대해 약간의 수정이 필요합니다., , MAMP의 mysql다입니다 두번째로,sed -rMac에서는 작동하지 않습니다.sed -E대신.root 사용자 기본값에 대한 변경 사항이 없는 표준 MAMP 설정을 가정할 때 [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"을(를) 교체합니다.-p비밀번호로

데이터베이스를 파일에 sqldump할 필요 없이 쿼리를 사용하여 삭제하려는 경우:

select concat('drop trigger ', trigger_name, ';') from information_schema.triggers where trigger_schema = 'your_schema';그런 다음 결과를 내보내고(CSV로 전송하는 것이 가장 쉽다고 생각합니다) 해당 결과의 쿼리를 실행해야 합니다.

빠른 대답은 "아니오" 입니다.

그러나 논리를 하나의 저장 프로시저로 캡슐화할 수 있습니다. 즉, 동적 SQL을 사용하여 모든 트리거를 가져오고 루프에서 모든 트리거를 삭제합니다.

이 후에는 다음을 실행할 수 있습니다.call delete_all_triggers_api(schema_name);

언급URL : https://stackoverflow.com/questions/12637945/how-can-i-delete-all-the-triggers-in-a-mysql-database-using-one-sql-statement