SQL:删除带有前缀的表

SQL: deleting tables with prefix(SQL:删除带有前缀的表)
本文介绍了SQL:删除带有前缀的表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何删除所有带有前缀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:删除带有前缀的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)
SQL/MySQL: split a quantity value into multiple rows by date(SQL/MySQL:按日期将数量值拆分为多行)