MySQL bulk delete table with same prefix and modify table name

If there are many tables that need to be deleted, and the table has the same prefix, we might 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 manually, we may need a lot of repetitive work and may not know the table name yet. So we can output the deletion table statement above through the SQL statement

Execute 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 the rules according to your own situation.

Executing the query automatically generates the matching table with SQL statements such as DROP table table_name.

Bulk copy to edit tools such as Notepad or ET to determine if your SQL statement is correct.

This can also be a safe audit of the statement to avoid misoperation.

Of course, this is only a thought, but also can be used in other issues.

For example, how to modify a table name in bulk:

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_% ';

Execute the query and get the result:

The code is as follows	Copy Code
ALTER TABLE uc_aaa RENAME to UC_AAA; ALTER TABLE uc_bbb RENAME to UC_BBB;

Bulk copy to Notepad or et such as editing tools, and then bulk replace RENAME to UC into RENAME to you want the table prefix
Completed before execution.