How to change a MySQL database’s table prefix

Changing a database table prefix is easy and here’s the simple step-by-step guide! For WordPress installations, it’s essential!

What you’ll need

  1. Use random.org to generate a strong table prefix
  2. Access to the database via cPanel or PHPMyAdmin
  3. A text editor
  4. 2 minutes

How to change a prefix

  1. In your text editor, change database_name, old_prefix_ and new_prefix_ to the required values:
    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;
    
  2. Run the query in cPanel or PHPMyAdmin on your WordPress database
  3. The output will be a series of SQL queries that will rename the tables for you
  4. Run the output
  5. Done!

How to add a prefix

If your database doesn’t have a prefix at all, follow the steps above but use the below query that’s been slightly modified for this purpose:

SET @database = "database_name";
SET @prefix   = "prefix_";

SELECT
    concat(
        "RENAME TABLE ",
        TABLE_NAME,
        " TO ",
        @prefix,
        TABLE_NAME,
        ';'
    ) AS "SQL"
FROM information_schema.TABLES WHERE TABLE_SCHEMA = @database;

Conclusion

See how easy that was? Isn’t it great when articles just get straight to the point? :)

4 Comments

  1. I’m getting an error at

    ) AS “SQL” – syntax error (double quoted text) is not valid input here.

    I have tried single quotes to no avail.

    • Hi Andrew, afraid I’m unable to reproduce. Are you running against a specific database and not the server? And which database engine are you using — MySQL, MariaDB, etc.?

      • Running on AWS RDS MySql and using the MySQLWorkBench remote connection.

        The result Grid looks correct but it doesn’t execute with a big red cross on that line..

        • Try removing AS "SQL" but be careful to keep the closing parenthesis at the start of that line. That bit is just an column alias and isn’t necessary, so if the results are fine then you should be good to go!

Leave a Reply

Your email address will not be published.