SQL:删除带有前缀的表

2022-01-05 00:00:00 mysql phpmyadmin sql-drop

如何删除所有带有前缀myprefix_的表?

How to delete my tables who all have the prefix myprefix_?

注意:需要在phpMyAdmin中执行

Note: need to execute it in phpMyAdmin

推荐答案

你不能只用一个 MySQL 命令来完成，但是你可以使用 MySQL 为你构造语句:

You cannot do it with just a single MySQL command, however you can use MySQL to construct the statement for you:

在 MySQL shell 中或通过 PHPMyAdmin，使用以下查询

In the MySQL shell or through PHPMyAdmin, use the following query

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_name LIKE 'myprefix_%';

这将生成一个 DROP 语句，您可以复制并执行该语句以删除表.

This will generate a DROP statement which you can than copy and execute to drop the tables.

此处免责声明 - 上面生成的语句将删除具有该前缀的所有数据库中的所有表.如果您想将其限制为特定的数据库，请将查询修改为如下所示并将 database_name 替换为您自己的 database_name:

A disclaimer here - the statement generated above will drop all tables in all databases with that prefix. If you want to limit it to a specific database, modify the query to look like this and replace database_name with your own database_name:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' ) 
    AS statement FROM information_schema.tables 
    WHERE table_schema = 'database_name' AND table_name LIKE 'myprefix_%';

相关文章