如何在 MySQL 中刪除所有表

Rayven Esplanada 2023年1月30日 2020年11月7日
  1. 刪除所有表的 SQL 查詢
  2. 清空和重新建立資料庫
  3. 驗證已刪除的表
如何在 MySQL 中刪除所有表

本教程演示了使用者在 MySQL 中刪除所有表的幾種方法,並列舉了實現這種方法的示例指令碼。

之所以不能在一行程式碼中直接刪除所有表,是因為在一個規模龐大且設計良好的資料庫中,很可能會有很多外來鍵約束。

刪除所有表的 SQL 查詢

  • 首先要做的就是讓刪除表不需要檢查外來鍵約束。
    SET FOREIGN_KEY_CHECKS = 0;
    
  • 然後繼續寫一個指令碼來查詢資料庫內的所有表。
    SELECT
        table_name
    FROM
        information_schema.tables
    WHERE
        table_schema = db_name;
    
  • 之後,將上述查詢結果中的所有表複製過來,逐一刪除。
    DROP TABLE IF EXISTS tableOne;
    DROP TABLE IF EXISTS tableTwo;
    DROP TABLE IF EXISTS tableThree;
    DROP TABLE IF EXISTS tableEtc;
    
  • 將外來鍵檢查的配置重置為預設值。
    SET FOREIGN_KEY_CHECKS = 1;
    

這種方法之所以能夠實現,是因為我們一開始就禁用了外來鍵檢查。所以無論我們以什麼順序刪除表,都不會丟擲任何約束錯誤。只要確保在指令碼之後重新啟用外來鍵檢查即可。

清空和重新建立資料庫

要做到這一點,你需要確保除了表之外,你還願意刪除儲存函式、索引、角色和表之外的其他設定,因為你要刪除整個資料庫。

要想成功執行,當前使用者必須在資料庫中擁有 dropcreate 許可權。

DROP DATABASE database_name;

刪除後,重新建立資料庫。

CREATE DATABASE database_name;

這種方法很可能是刪除所有表的最不穩定的方法。這僅適用於小型資料庫或實驗資料庫。

在 MySQL 中使用 mysqldump 刪除表

mysqldump 是 MySQL 的控制檯命令。假設你已經在路徑變數中設定了 MySQL,我們就可以使用該命令來刪除資料庫內的表。

  • 第一步仍然是在控制檯中禁用外來鍵檢查。
    echo "SET FOREIGN_KEY_CHECKS = 0" > ./temp.sql
    
  • 繼續使用 mysqldump 轉儲資料庫,並刪除表
    mysqldump --add-drop-table --no-data -u rootuser -p database_name | grep 'DROP TABLE' >> ./temp.sql
    
  • 在控制檯中重新啟用外來鍵檢查。
    echo "SET FOREIGN_KEY_CHECKS = 1;" >> ./temp.sql
    
  • 使用轉儲檔案恢復資料庫
    mysql -u root -p db_name < ./temp.sql
    

如果你對控制檯命令有一定的瞭解,並且在路徑變數中設定了 MySQL,這樣做會更容易。

驗證已刪除的表

為了驗證是否真的刪除了所有的表,我們可以用這個指令碼檢查資料庫是否還能輸出表。

SHOW TABLES FROM database_name;

如果沒有輸出,說明查詢已經執行成功,該特定資料庫的所有 SQL 表都被刪除了。

綜上所述,之所以不能直接在 SQL 中批量刪除表,是因為關係型資料庫嚴重依賴關係和約束。如果允許你在沒有明確禁止外來鍵檢查的情況下刪除表,那麼關係、約束和外來鍵配置就會被破壞。

Rayven Esplanada avatar Rayven Esplanada avatar

Skilled in Python, Java, Spring Boot, AngularJS, and Agile Methodologies. Strong engineering professional with a passion for development and always seeking opportunities for personal and career growth. A Technical Writer writing about comprehensive how-to articles, environment set-ups, and technical walkthroughs. Specializes in writing Python, Java, Spring, and SQL articles.

LinkedIn

相關文章 - MySQL Table