Find and Replace text in the entire table using a MySQL query

Running an SQL query in phpMyAdmin to find and replace text in all WordPress blog posts, such as finding mysite.example/wordpress and replacing that with mysite.example/news Table in this example is tj_posts

UPDATE `tj_posts`
SET `post_content` = replace(post_content, 'mysite.example/wordpress', 'mysite.example/news')

phpMyAdmin includes a neat find-and-replace tool.

Select the table, then hit Search > Find and replace

This query took about a minute and successfully replaced several thousand instances of oldurl.ext with the newurl.ext within Column post_content

Best thing about this method : You get to check every match before committing.

N.B. I am using phpMyAdmin 4.9.0.1

If you are positive that none of the fields to be updated are serialized, the solutions above will work well.

However, if any of the fields that need updating contain serialized data, an SQL Query or a simple search/replace on a dump file, will break serialization (unless the replaced string has exactly the same number of characters as the searched string).

To be sure, a "serialized" field looks like this:

a:1:{s:13:"administrator";b:1;}

The number of characters in the relevant data is encoded as part of the data. Serialization is a way to convert "objects" into a format easily stored in a database, or to easily transport object data between different languages. Here is an explanation of different methods used to serialize object data, and why you might want to do so, and here is a WordPress-centric post: Serialized Data, What Does That Mean And Why is it so Important? in plain language.

It would be amazing if MySQL had some built in tool to handle serialized data automatically, but it does not, and since there are different serialization formats, it would not even make sense for it to do so.

wp-cli Some of the answers above seemed specific to WordPress databases, which serializes much of its data. WordPress offers a command line tool, wp search-replace, that does handle serialization. A basic command would be:

    wp search-replace 'an-old-string' 'a-new-string' --dry-run

However, WordPress emphasizes that the guid should never be changed, so it recommends skipping that column. It also suggests that often times you'll want to skip the wp_users table. Here's what that would look like:

wp search-replace 'https://old-domain.example' 'https://shiney-new-domain.com' --skip-columns=guid --skip-tables=wp_users --dry-run

Note: I added the --dry-run flag so a copy-paste won't automatically ruin anyone's database. After you're sure the script does what you want, run it again without that flag.

Plugins If you are using WordPress, there are also many free and commercial plugins available that offer a gui interface to do the same, packaged with many additional features.

Interconnect/it PHP script Interconnect/it offers a PHP script to handle serialized data: Safe Search and Replace tool. It was created for use on WordPress sites, but it looks like it can be used on any database serialized by PHP. Many companies, including WordPress itself, recommends this tool. Instructions here, about 3/4 down the page.

UPDATE  `MySQL_Table` 
SET  `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString')
WHERE  `MySQL_Table_Column` LIKE 'oldString%';

the best you export it as sql file and open it with editor such as visual studio code and find and repalace your words. i replace in 1 gig file sql in 1 minutes for 16 word that total is 14600 word. its the best way. and after replace it save and import it again. do not forget compress it with zip for import.

Here's an example of how to find and replace in Database

 UPDATE TABLE_NAME
 SET COLUMN = replace(COLUMN,'domain.example', 'www.domain.example') 

TABLE_NAME => Change it with your table name

COLUMN => Change it to your column make sure it exists

Generate change SQL queries (FAST)

mysql -e "SELECT CONCAT( 'update ', table_name , ' set ', column_name, ' = replace(', column_name,', ''www.oldsite.example'', ''www.newsite.example'');' ) AS statement FROM information_schema.columns WHERE table_name LIKE 'wp_%'" -u root -p your_db_name_here > upgrade_script.sql

Remove any garbage at the start of the file. I had some.

nano upgrade_script.sql

Run generated script with --force options to skip errors. (SLOW - grab a coffee if big DB)

mysql -u root -p your_db_name_here --force < upgrade_script.sql

In the case of sentences with uppercase - lowercase letters, We can use BINARY REPACE

UPDATE `table_1`  SET  `field_1` =  BINARY REPLACE(`field_1`, 'find_string', 'replace_string')

I have good luck with this query when doing a search and replace in phpmyadmin:

UPDATE tableName SET fieldName1 = 'foo' WHERE fieldName1 = 'bar';

Of course this only applies to one table at a time.