Menu
My provider installed to my site Drupal CMS. Now I need copy all my data from old site. I have tables without prefixes in my old DB, but in new DB all tables have dp_[table_name] prefix.
732
Every so often you need to change your database prefix, whether it’s because you need to secure your WordPress website a little bit, or you want to make it easier to identify which tables are for what. Regardless, you need a simple way to change or remove your database table prefix.
How to change a database table prefix Log into phpMyadmin Select the database you wish to work with Click on the “Structure” tab; all or most of your tables should be listed Go to bottom of screen, click on “Check all” Change the select box next to it and select “Replace Table Prefix” A modal will popup In From, enter your old prefix.
What if you don’t have a table prefix and wish to add a prefix? Again, if you have a wordpress website, you could use this to change the db prefix for WordPress. That’s it!
zerkms solution didn't work for me. I had to specify the information_schema database to be able to query the Tables table.
SELECT CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`prefix_', TABLE_NAME, '`')) AS q FROM `information_schema`.`Tables` WHERE TABLE_SCHEMA='test'; Edit:
Optimized the query to only call RENAME TABLE once. Something I walked into was the fact that the concatenated output got truncated at 341 characters. This can be solved (if allowed by your server) by setting the MySQL variable group_concat_max_len to a higher value:
SET group_concat_max_len = 3072; -- UTF8 assumes each character will take 3 bytes, so 3072/3 = 1024 characters. If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
