How to Change WordPress Table Prefix of mySQL Database

If you like to change the WordPress table prefix of the database on an existing WordPress website  you can use the following SQL query:

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;

This query will create another SQL query which you can use to rename all table prefixes to the new ones.

After doing that you also need to replace some values in the table wp_usermeta and wp_options by using the queries below:

UPDATE `newprefix_usermeta`
SET meta_key = REPLACE(meta_key, 'oldprefix', 'newprefix_')
WHERE meta_key LIKE 'oldprefix_%';


UPDATE wp_options SET meta_key = replace(meta_key, 'oldprefix_', 'newprefix_') WHERE option_names LIKE 'oldprefix_%';

That’s it.