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
- Use random.org to generate a strong table prefix
- Access to the database via cPanel or PHPMyAdmin
- A text editor
- 2 minutes
How to change a prefix
- In your text editor, change
database_name
,old_prefix_
andnew_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;
- Run the query in cPanel or PHPMyAdmin on your WordPress database
- The output will be a series of SQL queries that will rename the tables for you
- Run the output
- 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? :)
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!