Rename A Table Prefix Using Wildcards

I'm trying to change the prefix on all the tables in my site. I thought I could do thi开发者_StackOverflow社区s:

rename table 'wp_%' to 'wp_13_%'

But that doesn't seem to work. I'd love input into what i may be doing wrong.

Thanks!

Since mysql doesn't use wildcards like % in rename table why don't you export the database do a global seach in your favorite text editor wp_ and replace with wp_13_?

Then you can reimport it.

That should do it.

I know you're using MySQL, though I have something like that written for SQL Server (using T-SQL.) It's like using a bulldozer to open a door, but it works. You could possibly use it as a basis for a MySQL query... definitely not an instantly usable answer, but something that you could base the logic for a stored procedure off if you wanted.

DECLARE @FrontWord nvarchar(20)
DECLARE @NewFrontWord nvarchar(20)

SET @FrontWord = 'wp_'
SET @NewFrontWord = 'wp_13_'

DECLARE MY_CURSOR Cursor
FOR (
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_NAME LIKE @FrontWord + '%'
)

Open MY_CURSOR

DECLARE @tablename nvarchar(20)
DECLARE @lastpart nvarchar(20)
DECLARE @newtablename nvarchar(20)

Fetch NEXT FROM MY_Cursor INTO @tablename

    While (@@FETCH_STATUS <> -1)
    BEGIN
        IF (@@FETCH_STATUS <> -2)
        SET @lastpart = RIGHT(@tablename, LEN(@tablename) - LEN(@FrontWord))
        SET @newtablename = @NewFrontWord + @lastpart;
        PRINT @newtablename

        EXEC sp_rename @tablename, @newtablename

FETCH NEXT FROM MY_CURSOR INTO @tablename
END 

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

mysql rename syntax doesn't support wildcards like the % you used. You can still rename several tables in one command.

e.g. if you have table wp_A, wp_B you can rename to wp_13_A, wp_13_B

RENAME TABLE 'wp_A' TO 'wp_13_A', 'wp_B' TO 'wp_13_B'

You can generate the neccesary DDL code with sql. Not pretty, but powerful :)

Just copy&paste the result.

set session sql_mode='PIPES_AS_CONCAT';

select 'alter table ' 
     || table_name 
     || ' rename to wp_13_' 
     || substr(table_name, 4) 
     || ';' as ddl
  from information_schema.tables 
 where table_schema = 'BLOG';


+-----------------------------------------------------------------------+
| ddl                                                                   |
+-----------------------------------------------------------------------+
| alter table wp_commentmeta rename to wp_13_commentmeta;               |
| alter table wp_comments rename to wp_13_comments;                     |
| alter table wp_links rename to wp_13_links;                           |
| alter table wp_options rename to wp_13_options;                       |
| alter table wp_postmeta rename to wp_13_postmeta;                     |
| alter table wp_posts rename to wp_13_posts;                           |
| alter table wp_term_relationships rename to wp_13_term_relationships; |
| alter table wp_term_taxonomy rename to wp_13_term_taxonomy;           |
| alter table wp_terms rename to wp_13_terms;                           |
| alter table wp_usermeta rename to wp_13_usermeta;                     |
| alter table wp_users rename to wp_13_users;                           |
+-----------------------------------------------------------------------+