phpMyAdmin How to modify MySQL data table prefixes in bulk

When multiple Web sites share a MySQL database, to make the database management is not confusing, the general use of different sites using different prefix name of the way to differentiate. And how to bulk modify the existing database prefix name it? Export all changes before importing? Or is it a table-by-table modification? Today, I would like to introduce a relatively simple method of modifying the table prefixes in the database in batches, which is suitable for modifying the same prefix in the database and more data tables.

In this example, it is assumed that a table with the prefix "phpcms_" in the database named "WWW_SDCK_CN" is modified and that all qualifying table prefixes are modified to "sdck_".

1. With phpMyAdmin Open the database www_sdck_cn to modify the prefix, execute the following SQL statement (where bold italic is required to be replaced by actual requirements):

Select CONCAT (' ALTER TABLE ', table_name, ' RENAME to ', replace (table_name, 'phpcms_', 'sdck_'), '; ')

From Information_schema.tables

where Table_schema = 'www_sdck_cn' and table_name like 'phpcms_% ';


2. Click "Export" on the page that executes the SQL statement, select "Customize", "Show as text directly" and export the TXT format.

3. After performing the export function, the following results window is reached, and the contents of the text box are copied-------here because of different versions, you might need to deal with them first.

4. Execute the alert statement in TXT

The exported alert statement is similar to the

ALTER TABLE v9_admin RENAME to Yanglao_admin;

phpMyAdmin How to modify MySQL data table prefixes in bulk