How to search and replace all instances of a strin

问题:

I have a string that is contained inside of a wordpress install (the name of a server) thousands of times, across multiple columns, records and tables.

I'd like to update it with the location of another server - we are moving the content over.

So the source would be something like http://my-server1/some/link/to/something, and I'd want to replace it with http://my-other-server/some/link/to/something. I'm essentially looking to repeat this process for every instance of http://my-server1.

Is there an easy way to do this in MySQL? A tool? Or do I sadly have to update every record problematically?

Thank you,

回答1:

A crude (but effective) way of doing it would be to dump the schema into a file, carefully apply the search-and-replace and then re-import.

As a matter of fact I did that today :)

回答2:

Came across this in a google search, but this may help some people. If you know the tables and columns (you could find this using the wildcard search in phpMyAdmin),

UPDATE table_name SET column_name = REPLACE(column_name, 'http://oldsite.com','http://newsite.com');

Replace bold parts with your own.

If you had a large database you could apply this into a script that could loop through each table and column.

回答3:

The MySQL dump method would be the best bet if you're happy to re-import the whole database. For anyone that doesn't want to do this - WordPress core installation only actually consists of 11 tables, of which few are content columns, so doing a replace by column would be equally easy. Assuming you don't have loads of plugin tables referencing your link or string this would be your SQL:

UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_comments SET comment_content = REPLACE(comment_content,'xcurrentx','xreplacementx');
UPDATE wp_links SET link_description = REPLACE(link_description,'xcurrentx','xreplacementx');
UPDATE wp_options SET option_value = REPLACE(option_value,'xcurrentx','xreplacementx');
UPDATE wp_postmeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_content = REPLACE(post_content,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_title = REPLACE(post_title,'xcurrentx','xreplacementx');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt,'xcurrentx','xreplacementx');
UPDATE wp_term_taxonomy SET description = REPLACE(description,'xcurrentx','xreplacementx');
UPDATE wp_usermeta SET meta_value = REPLACE(meta_value,'xcurrentx','xreplacementx');

回答4:

Your question has been posted on 2009 and during that year another guy developed a basic php tool to search for a string throughout all the tables of a certain database. Optionally you can also replace all its occurrences with a different string.

Still after 5 years (at time of writing) the use of this tool against a Wordpress installation is effective and much easier in my opinion than using a mysql dump (although you might want to create a dump as well before running the script for backup purposes).

You can find more info in the blog of its author Eric Amundson and in the launchpad's page of the project MySQL Search & Replace

I don't believe you are still looking for an answer after all this time, but I decided to post my hint as well, hoping it can help someone else popping over here in the future still searching for a solution to this issue.

回答5:

There is also a Wordpress plugin called Search Regex, which allows running grep search and replace across the database.

回答6:

Addittionally to Brad Larson's answer - to set variables like:

SET @what_to_be_replaced = "what_to_be_replaced", @to_be_replaced_by = "to_be_replaced_by";

And then use it as follows:

UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value, @what_to_be_replaced, @to_be_replaced_by );

回答7:

MySQL's REPLACE or replacing the string manually in sql file or db in WordPress is not a good way. WordPress uses serialized fields that may break if you replace some string in them (that was my case)

I used Better Search Replace plugin that worked for me perfectly