Mysql batch deletes table prefixes or suffixes, and mysql batch deletes tables

Source: Internet
Author: User

Mysql batch deletes table prefixes or suffixes, and mysql batch deletes tables

Oracl has the usage of drop table like, but mysql does not. You can write scripts (not to repeat them) or assemble SQL statements.

Note: My database name is test and there is a table named data. Then I prepare four tables with the same prefix and delete them in batches. This SQL statement can be used to prepare data.

Create table test_1201 SELECT * FROM 'data ';
Create table test_1202 SELECT * FROM 'data ';
Create table test_1203 SELECT * FROM 'data ';
Create table test_1205 SELECT * FROM 'data ';
Select concat ('drop table', GROUP_CONCAT (table_name), ';') AS statement FROM information_schema.tables WHERE table_schema = 'test' AND table_name LIKE 'test _ 100 ';

The preceding SQL statement displays an SQL result. You can run the preceding SQL result on one side.

(Change table_schema (this is the database name) and table_name (this is the name of the data table)
Drop table test_1201, test_1202, test_1203;

Mysql Databases batch delete data tables with the same prefix

Create a delete script first

Run the following statement:
Select 'drop table' + name from sysobjects where type = 'U' and name like 'B %'

The query results are displayed, and the query results are copied.
Then run the copied query results in database 123.

Mysql Database SQL statements batch delete data tables with the same prefix

Delete tablename;
You can also use the client to delete mysqlfront, phpadmin...

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.