How to change WordPress table prefix of mySQL database – Learn WordPress from a to z

How to change the WordPress table prefix of mySQL database

If you want to change the WordPress table prefix of the database on an existing WordPress site, you can use the following SQL query where you have to update the title and SET attribute according to your needs:

. database name

. oldprefix_

. newprefix_

SQL Query

SET @database  = "databasename";
SET @oldprefix = "oldprefix_";
SET @newprefix = "newprefix_";
 
SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        replace(TABLE_NAME, @oldprefix, @newprefix),
        ';'
    ) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;
<div>
<span>1</span><span>2</span><span>3</span><span>4</span><span>5</span><span>6</span><span>7</span><span>8</span><span>9</span><span>10</span><span>11</span><span>12</span><span>13</span>
</div>

This query will generate many SQL queries like

RENAME oldprefix_options to newprefix_options;
RENAME oldprefix_users to newprefix_users;
<div>
<span>1</span><span>2</span>
</div>

Copy these queries and execute them to rename all tables to new tables.

After renaming all the tables, you also need to replace some values ​​in the *_usermeta and *_options tables using the below queries.

Don’t forget to update the values ​​newprefix_ , oldprefix_ again

To replace the values ​​in the table *_usermeta , use the following query:

UPDATE `<strong>newprefix_</strong>usermeta`
SET meta_key = REPLACE(meta_key, '<strong>oldprefix_</strong>', '<strong>newprefix_</strong>')
WHERE meta_key LIKE '<strong>oldprefix_</strong>%';
<div>
<span>1</span><span>2</span><span>3</span>
</div>

To replace the values ​​in the *_options table , use the following query:

UPDATE `<strong>newprefix_</strong>options` 
SET option_value = replace(option_value, '<strong>oldprefix_</strong>', '<strong>newprefix_</strong>') 
WHERE option_name LIKE '<strong>oldprefix_</strong>%';<div>
<span>1</span><span>2</span><span>3</span>
</div>

It is done. You can also learn more in depth through this article

Epilogue

You are done with changing the WordPress table prefix of the mySQL database.

If you find it interesting, you can follow the  WordPress basics section  to know more new knowledge.

Follow fanpage to receive the latest posts:  Group