如何在 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