*

Cómo buscar y reemplazar una palabra en una tabla Mysql

Cómo buscar y reemplazar una palabra en una tabla Mysql desde consola:

UPDATE tabla SET campo = REPLACE(campo,'texto a buscar','nuevo texto');

Ejemplo en WP. Cambira de old.mydomain.com a newdomain.com

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');

Usando php:

// Conexión con la base de datos MySQL.
$hostname = "localhost";
$username = "db_username";
$password = "db_password";
$database = "db_name";

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

// Busca y reemplaza palabra.
$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']);
}

Con dos bases de datos

bbdd1.user :

id_user : 45 | name_user : Test | login_user : test | pwd_user : test
[...]

bbdd2.user:

id_user : 45 | name_user : Test | login_user : test | pwd_user : test
[...]

Para actualizar todos los campos «pwd_user» en «bbdd2.user» con los valores de pwd_user de «bbdd1.user»:

UPDATE 
 bbdd1.user, 
 bbdd2.user 
SET 
 bbdd2.user.pwd_user = bbdd1.user.pwd_user
WHERE 
 bbdd2.user.name_user = bbdd1.user.name_user;