HOWTO · MySQL
MySQL에서 모든 테이블을 삭제하는 방법
MySQL 스키마의 모든 기본 테이블을 안전하게 삭제하고 외래 키를 처리한 뒤 결과를 확인합니다.
이 페이지의 내용
MySQL 데이터베이스 자체는 유지하면서 모든 테이블을 삭제하려면 스키마의 BASE TABLE 객체에 대해 검토한 DROP TABLE 문 하나를 생성하고, FOREIGN_KEY_CHECKS를 일시적으로 끈 같은 세션에서 실행한 뒤 결과를 확인합니다. 테이블 정의와 데이터는 영구적으로 삭제되므로 먼저 백업하고 선택한 스키마를 삭제해도 되는지 확인하십시오.
이 작업에는 DROP TABLE 사용
DROP TABLE은 테이블을 제거합니다. 행만 지우고 테이블 정의는 남기는 DELETE, 테이블을 비우는 TRUNCATE와는 다릅니다. DROP DATABASE는 데이터베이스 컨테이너와 스키마 수준 객체까지 제거하는 더 넓은 별도 작업이므로, 그것까지 제거할 의도가 있을 때만 사용하십시오. 테이블을 삭제하면 트리거도 삭제되고 암시적 커밋이 발생하므로 트랜잭션에서 시험한 후 롤백할 작업이 아닙니다.
삭제할 테이블에는 DROP 권한이 필요합니다. 이름을 명확히 지정한 스키마에서 작업하고 백업 또는 안전하게 폐기할 수 있는 테스트 데이터베이스를 사용하며, 준비·삭제·복원 문을 같은 MySQL 연결에서 실행하십시오.
따옴표로 묶인 기본 테이블 목록 생성
INFORMATION_SCHEMA.TABLES는 BASE TABLE과 뷰를 구별합니다. 따라서 table_type = 'BASE TABLE' 필터를 사용하면 유지할 뷰를 테이블로 취급하여 삭제하지 않습니다. 아래 쿼리는 식별자를 백틱으로 감싸고, 문을 만들기 전에 테이블 이름 안의 백틱을 두 번 씁니다.
스키마 안 이름의 수와 길이에 맞게 group_concat_max_len을 충분히 크게 설정하십시오. IF 식은 빈 스키마에서 이름 없는 DROP TABLE을 시도하는 대신 무해한 메시지를 만듭니다. 운영과 유사한 환경에서 실행하기 전에 생성된 @drop_sql 값을 검토하십시오.
한 세션에서 외래 키로 연결된 기본 테이블 삭제
외래 키 순서 때문에 개별 삭제가 실패할 수 있습니다. 현재 세션 값을 저장하고 이 통제된 세션에서만 검사를 끈 뒤 생성한 문을 실행하고 저장한 값을 복원합니다. 다음의 정확한 MySQL 8.4.11 fixture는 외래 키로 연결된 두 테이블과 뷰 하나를 만들고, 테이블만 삭제하며, 전후 개수를 출력합니다.
#!/bin/sh
# Verify that a generated DROP TABLE statement removes FK-linked base tables.
# A view is retained deliberately so the fixture also proves the table/view boundary.
set -eu
fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
--pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1
mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE drop_all_demo;
USE drop_all_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (
id INT PRIMARY KEY,
parent_id INT,
CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id)
);
CREATE VIEW parent_ids AS SELECT id FROM parent;
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SET SESSION group_concat_max_len = 1048576;
SELECT GROUP_CONCAT(
CONCAT('`', REPLACE(table_name, '`', '``'), '`')
ORDER BY table_name SEPARATOR ', '
)
INTO @table_list
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SET @drop_sql = IF(
@table_list IS NULL,
'SELECT ''No base tables found''',
CONCAT('DROP TABLE IF EXISTS ', @table_list)
);
SET @saved_fk_checks = @@SESSION.foreign_key_checks;
SET SESSION foreign_key_checks = 0;
PREPARE drop_statement FROM @drop_sql;
EXECUTE drop_statement;
DEALLOCATE PREPARE drop_statement;
SET SESSION foreign_key_checks = @saved_fk_checks;
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE';
SELECT COUNT(*)
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'VIEW';
SELECT @@SESSION.foreign_key_checks;
SQL
2
0
1
1
출력은 처음에 기본 테이블이 둘이고 삭제 후에는 없으며, 뷰 하나는 남고 FOREIGN_KEY_CHECKS가 원래의 활성 값으로 복원되었음을 뜻합니다. 이 방법은 뷰, 저장 프로시저, 이벤트, 사용자 또는 데이터베이스 자체를 삭제하지 않습니다.
삭제 후 스키마 확인
fixture에서 사용한 동일한 INFORMATION_SCHEMA.TABLES 개수를 실행하여 BASE TABLE 행이 0개인지 확인하십시오. 남은 객체가 테이블이 아니라 뷰인지 검사해야 할 때는 SHOW FULL TABLES FROM your_database도 유용합니다. 스크립트가 FOREIGN_KEY_CHECKS를 바꾸면 연결을 닫기 전에 세션 값을 확인하십시오. 다른 세션이 그 변경을 상속한다고 가정하면 안 됩니다.
외래 키 실패 경계 이해
검사를 일시적으로 끄지 않으면 MySQL은 참조되는 부모 테이블을 삭제하려는 시도를 거부합니다. 이 경계 fixture는 오류 3730으로 끝나며, 순서가 없는 DROP TABLE 명령 목록이 실패할 수 있는 이유를 보여 줍니다.
#!/bin/sh
# Demonstrate the foreign-key failure when a referenced table is dropped normally.
set -eu
fixture_tmp=$(mktemp -d)
trap 'kill "$server_pid" 2>/dev/null || true; rm -rf "$fixture_tmp"' EXIT INT TERM
mysqld --no-defaults --initialize-insecure --datadir="$fixture_tmp/data" --user=root >/dev/null 2>&1
mysqld --no-defaults --datadir="$fixture_tmp/data" --socket="$fixture_tmp/mysql.sock" \
--pid-file="$fixture_tmp/mysql.pid" --skip-networking --user=root >/dev/null 2>&1 &
server_pid=$!
for _ in $(seq 1 30); do
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1 && break
sleep 1
done
mysqladmin --no-defaults --socket="$fixture_tmp/mysql.sock" ping >/dev/null 2>&1
mysql --no-defaults --batch --raw --skip-column-names --socket="$fixture_tmp/mysql.sock" <<'SQL'
CREATE DATABASE fk_boundary_demo;
USE fk_boundary_demo;
CREATE TABLE parent (id INT PRIMARY KEY);
CREATE TABLE child (id INT PRIMARY KEY, parent_id INT, CONSTRAINT child_parent_fk FOREIGN KEY (parent_id) REFERENCES parent(id));
DROP TABLE parent;
SQL
캡처된 진단은 ERROR 3730 (HY000)입니다. MySQL은 parent가 child의 child_parent_fk에서 참조된다고 보고합니다. 알 수 없는 종속성을 우회하려고 검사 비활성화 기법을 사용하지 마십시오. 모든 기본 테이블 삭제가 의도된 일임을 확인한 뒤에만 사용하십시오.
폐기 가능한 데이터베이스와 명령줄 자동화의 대안
완전히 폐기 가능한 스키마라면 데이터베이스를 삭제하고 다시 만드는 편이 더 간단할 수 있지만, 이는 테이블 삭제보다 범위가 넓습니다. 적절한 데이터베이스 수준 권한이 필요하고 기본 테이블 방법이 의도적으로 남기는 객체도 제거합니다. 뷰, 루틴 또는 이벤트를 보존하는 방법이라고 설명하지 마십시오.
mysqldump --add-drop-table --no-data는 셸 지향 삭제 스크립트를 만들 수 있으며 검토 후 자동화에 유용합니다. 암호를 명령줄에 직접 넣지 말고 생성된 문을 검사하며, 자격 증명과 이식성 문제는 권장 SQL 방법과 분리해서 다루십시오.
제한 사항 및 문제 해결
테이블이 하나도 삭제되지 않으면 먼저 선택한 스키마와 BASE TABLE 필터를 확인하십시오. 매우 큰 스키마는 GROUP_CONCAT 한도를 넘을 수 있으므로 의도적으로 한도를 올리거나 문을 일괄 생성해 검토하십시오. 권한 오류는 계정에 DROP 접근 권한이 없다는 뜻입니다. 뷰는 설계상 남으므로 승인된 정리에 포함될 때만 따로 삭제합니다. 마지막으로 DROP TABLE은 암시적으로 커밋하므로 롤백을 기대하지 말고 백업에서 복원하십시오.
