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? :)