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 資料列。若要確認剩餘物件是檢視表而非資料表，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 可產生面向 shell 的刪除腳本，檢查後適合用於自動化。不要直接在命令列寫入密碼，請檢視產生的陳述式，並將認證資料與可攜性的考量和建議的 SQL 方法分開處理。
限制與疑難排解
若沒有任何資料表被刪除，請先確認選定的綱要和 BASE TABLE 篩選器。很大的綱要可能超出 GROUP_CONCAT 限制；請有意識地提高限制，或分批產生並檢查陳述式。權限錯誤表示帳戶沒有 DROP 存取權。檢視表會依設計保留；只有在核准的清理工作包含它們時才個別刪除。最後，DROP TABLE 會隱含提交，應由備份還原，而不是期待復原找回刪除的資料。
