Mysql deletes tables with the specified prefix in batches and modifies the SQL statement _ MySQL

Mysql deletes tables with the specified prefix in batches and modifies the SQL statement bitsCN.com of table names in batches.

Mysql deletes tables with a specified prefix in batches and modifies SQL statements of table names in batches.

Select CONCAT ('drop table', table_name ,';')

FROM information_schema.tables Where table_name LIKE 'UC _ % ';

Note: like 'UC _ % 'where uc _ is the table prefix you need to replace.

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

Then copy the drop statement to perform the delete operation.

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

By the way, add the next operation method to modify table names in batches.

Select CONCAT ('alter table', table_name, 'Rename to', table_name ,';')

FROM information_schema.tables Where table_name LIKE 'UC _ % ';

The following code is met today. The header is in front of the db, but it looks messy without a horizontal line. Therefore, "dbtable_name" is changed to "db_table_name" in batches"

The main function used is the substring function of mysql.

Mysql tutorial substring character truncation function

Substring (str, pos) syntax

Substring (filed, m): the string that intercepts the filed field from the m character to the end;

Substring (filed, m, n): truncates a string whose length starts from the nth character of the filed field;

Str, character

Pos, starting from the Nth

Select CONCAT ('alter table', table_name, 'Rename TO db _ ', substring (table_name, 3 ),';')

FROM information_schema.tables Where table_name LIKE 'DB % ';

The result is displayed.

Alter table uc_aaa rename to uc_aaa;

Alter table uc_bbb rename to uc_bbb;

BitsCN.com