Mysql batch deletes tables with the same prefix and modifies table names

This article introduces how to batch Delete tables with the same prefix and modify table names in mysql.

If many tables need to be deleted and the table has the same prefix, we may need the following statement:

The Code is as follows:	Copy code
Drop table pre_tablename1; Drop table pre_tablename2; Drop table pre_tablename3;

... If we write data manually, it may take a lot of repetitive work and may not know the table name. Therefore, you can use SQL statements to output the preceding table deletion statement.

Execute the SQL statement:

The Code is as follows:	Copy code
Select CONCAT ('drop table', table_name ,';') FROM information_schema.tables Where table_name LIKE 'pre _ % ';

Note: like 'pre _ % ', where pre _ is the table prefix you need to replace. Of course, you can also write rules based on your own situation.

When the query is executed, an SQL statement such as drop table table_name is automatically generated for the matched table.

Copy the SQL statement to notepad, et, and other editing tools in batches to check whether your SQL statement is correct.

In this way, you can also securely review statements to avoid misoperations ..

Of course, this is just a way of thinking and can also be used in other problems.

For example, how to modify table names in batches:

The Code is as follows:	Copy code
Select CONCAT ('alter table', table_name, 'rename to', table_name ,';') FROM information_schema.tables Where table_name LIKE 'uc _ % ';

Run the query and the result is:

The Code is as follows:	Copy code
Alter table uc_aaa rename to uc_aaa; Alter table uc_bbb rename to uc_bbb;

Copy it TO notepad, et, and other editing tools in batches, and replace rename to uc with rename to the table prefix you want.
And then execute.