CODEWITHSUNDEEP
mysqldatabaseprefix
SkyFox
SkyFox

Reputation: 1875

How I can change prefixes in all tables in my MySQL DB?

My provider installed to my site Drupal CMS. Now I need copy all my data from old site. I have tables without prefixes in my old DB, but in new DB all tables have dp_[table_name] prefix.

Upvotes: 29

Views: 54609

Answers (7)

Kashif Saeed
Kashif Saeed

Reputation: 147

SET @database   = "Database-Name-Here";
SET @old_prefix = "wp_";
SET @new_prefix = "ab_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        @database,
        ".",
        TABLE_NAME,
        " TO ",
        @database,
        ".",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

Replace "Database-Name-Here" with the name of your database, "wp_" with the old prefix, and "ab_" with the new prefix. The preceding code will produce queries for the MySQL DB, also, the queries created by the above code are surrounded by single quotes, which must be replaced. However, if you are dealing with WordPress, you will need to do some more steps otherwise your site will be broken. Following the queries created by the aforementioned code, the following queries must be executed.

update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_capabilities' where meta_key = 'OLDPREFIX_capabilities';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_user_level' where meta_key = 'OLDPREFIX_user_level';
update NEWPREFIX_usermeta set meta_key = 'NEWPREFIX_autosave_draft_ids' where meta_key = 'OLDPREFIX_autosave_draft_ids';
update NEWPREFIX_options set option_name = 'NEWPREFIX_user_roles' where option_name = 'OLDPREFIX_user_roles'

Replace "NEWPREFIX" with your new prefix and "OLDPREFIX" with your old prefix.

Upvotes: 3

fsjk85
fsjk85

Reputation: 61

Just modded this slightly to account for situations where the prefix is also in the table name.

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    CONCAT(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        CONCAT(@new_prefix, TRIM(LEADING @old_prefix FROM TABLE_NAME)),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

Upvotes: 6

Juli15
Juli15

Reputation: 411

If there's someone out there yet wondering how to do this (as it did not work form me the other options) you can run this (changing the first three variables for your values, of course):

SET @database   = "database_name"; 
SET @old_prefix = "old_prefix_"; 
SET @new_prefix = "new_prefix_";
   SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @old_prefix, @new_prefix),
        ';'
    ) AS "SQL" FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

And then you will be prompted with a bunch of queries needed in order to change all the tables in your database. You simply have to copy that, run it and voilá!

Upvotes: 3

foray
foray

Reputation: 401

PhpMyAdmin allows you to do this now. At the "Database" level select the Structure tab to see all the tables. Click 'check all' (below the table listing). On the 'With selected' dropdown choose: 'Replace table prefix'.

Upvotes: 30

Koen.
Koen.

Reputation: 26939

zerkms solution didn't work for me. I had to specify the information_schema database to be able to query the Tables table.

SELECT 
    CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='test';

Edit:

Optimized the query to only call RENAME TABLE once. Something I walked into was the fact that the concatenated output got truncated at 341 characters. This can be solved (if allowed by your server) by setting the MySQL variable group_concat_max_len to a higher value:

SET group_concat_max_len = 3072; -- UTF8 assumes each character will take 3 bytes, so 3072/3 = 1024 characters.

Upvotes: 32

zerkms
zerkms

Reputation: 254886

write a script that will run RENAME TABLE for each table.

SELECT 
  GROUP_CONCAT('RENAME TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`;' SEPARATOR ' ')
FROM 
  `TABLES` WHERE `TABLE_SCHEMA` = "test";

where "test" is expected database name

after this you can long query that will add prefixes if you execute it ;-)

Upvotes: 21

Tomislav Nakic-Alfirevic
Tomislav Nakic-Alfirevic

Reputation: 10173

You can simply dump the database, open the dump with a text editor, replace all occurrences of "CREATE TABLE " with "CREATE TABLE dp_" and restore the database. It takes a couple of minutes to do.

Upvotes: 5

Related Questions