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;