SQL:删除带前缀的表 [英] SQL: deleting tables with prefix

问题描述

如何删除所有前缀为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_%';

这篇关于SQL:删除带前缀的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！