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

Question

For a single table update

 UPDATE `table_name`
 SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

From multiple tables-

If you want to edit from all tables, best way is to take the dump and then find/replace and upload it back.

The easiest way I have found is to dump the database to a text file, run a sed command to do the replace, and reload the database back into MySQL.

All commands below are bash on Linux.

Dump database to text file

mysqldump -u user -p databasename > ./db.sql

Run sed command to find/replace target string

sed -i 's/oldString/newString/g' ./db.sql

Reload the database into MySQL

mysql -u user -p databasename < ./db.sql

Easy peasy.

Put this in a php file and run it and it should do what you want it to do.

// Connect to your MySQL database.
$hostname = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

mysql_connect($hostname, $username, $password);

// The find and replace strings.
$find = "find_this_text";
$replace = "replace_with_this_text";

$loop = mysql_query("
    SELECT
        concat('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=replace(',column_name,', ''{$find}'', ''{$replace}'');') AS s
    FROM
        information_schema.columns
    WHERE
        table_schema = '{$database}'")
or die ('Cant loop through dbfields: ' . mysql_error());

while ($query = mysql_fetch_assoc($loop))
{
        mysql_query($query['s']);
}

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

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