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 の正確なフィクスチャは、外部キーで結ばれた二つのテーブルと一つのビューを作成し、テーブルだけを削除して前後の件数を表示します。
#!/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 が元の有効な値に復元されたことを示します。この方法で削除されるのはテーブルだけで、ビュー、ストアドプロシージャ、イベント、ユーザー、データベース自体は削除されません。
削除後にスキーマを検証する
フィクスチャと同じ INFORMATION_SCHEMA.TABLES の件数取得を実行し、BASE TABLE 行がゼロであることを確認します。残ったオブジェクトがテーブルではなくビューかを調べるには、SHOW FULL TABLES FROM your_database も役立ちます。スクリプトが FOREIGN_KEY_CHECKS を変更した場合は、接続を閉じる前にそのセッション値を確認してください。別のセッションが変更を引き継ぐことはありません。
外部キーで失敗する境界を理解する
チェックを一時的に無効にしなければ、MySQL は参照されている親テーブルを削除しようとする試みを拒否します。この境界用フィクスチャはエラー 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 は暗黙にコミットするので、削除したデータはロールバックではなくバックアップから復元してください。
