使用MySQL查询查找并替换整个表中的文本

通常我使用手动查找替换文本在MySQL数据库使用phpMyAdmin。我现在厌倦了，我怎么能运行一个查询，在phpMyAdmin的整个表中找到和替换一个新的文本?

例如:查找关键字domain.example，将其替换为www.domain.example。

582023 次浏览
小开
最佳答案

用于single table更新

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

从multiple tables -

如果你想从所有表中编辑，最好的方法是先取dump，然后再取find/replace，然后再上传回来。

小开

在phpMyAdmin中运行SQL查询来查找和替换所有WordPress博客文章中的文本，例如查找mysite.example/wordpress并用mysite.example/news替换它 本例中的表为tj_posts

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

把它放到一个php文件中，然后运行它，它应该做你想做的事情。

// 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']);
}
小开 
 UPDATE table SET field = replace(field, text_needs_to_be_replaced, text_required);

比如，如果我想把所有出现的约翰替换为马克，我会用下面的方法，

UPDATE student SET student_name = replace(student_name, 'John', 'Mark');
小开

我发现的最简单的方法是将数据库转储到一个文本文件中，运行sed命令进行替换，然后将数据库重新加载回MySQL。

下面所有命令都是Linux下的bash命令。

转储数据库到文本文件

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

执行sed命令查找/替换目标字符串

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

重新加载数据库到MySQL

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

容易peasy。

小开

另一种选择是为数据库中的每一列生成语句:

SELECT CONCAT(
'update ', table_name ,
' set ', column_name, ' = replace(', column_name,', ''www.oldDomain.example'', ''www.newDomain.example'');'
) AS statement
FROM information_schema.columns
WHERE table_schema = 'mySchema' AND table_name LIKE 'yourPrefix_%';

这将生成一个更新语句列表，然后您可以执行这些更新语句。

小开

我相信“swapnesh”答案是最好的!不幸的是，我不能执行它在phpMyAdmin(4.5.0.2)谁虽然不合逻辑(并尝试了几件事)，它一直说，一个新的语句被发现，没有分隔符被发现…

因此，我提出了以下解决方案，如果您遇到同样的问题，并且除了PMA之外没有其他访问数据库的方法，可能会很有用……

UPDATE `wp_posts` AS `toUpdate`,
(SELECT `ID`,REPLACE(`guid`,'http://old.tld','http://new.tld') AS `guid`
FROM `wp_posts` WHERE `guid` LIKE 'http://old.tld%') AS `updated`
SET `toUpdate`.`guid`=`updated`.`guid`
WHERE `toUpdate`.`ID`=`updated`.`ID`;

要测试预期的结果，您可能需要使用:

SELECT `toUpdate`.`guid` AS `old guid`,`updated`.`guid` AS `new guid`
FROM `wp_posts` AS `toUpdate`,
(SELECT `ID`,REPLACE(`guid`,'http://old.tld','http://new.tld') AS `guid`
FROM `wp_posts` WHERE `guid` LIKE 'http://old.tld%') AS `updated`
WHERE `toUpdate`.`ID`=`updated`.`ID`;
小开

生成变更SQL查询(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

删除文件开头的任何垃圾。我吃了一些。

nano upgrade_script.sql

使用——force选项运行生成的脚本以跳过错误。(慢速-如果DB大，可以喝杯咖啡)

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

小开

最好你把它导出为SQL文件，用visual studio代码等编辑器打开它，找到并重新排版你的文字。 我在1分钟内替换1gig文件SQL 16个字，总共是14600个字。 这是最好的办法。 替换后保存并再次导入。

小开

如果您确定要更新的字段都没有被序列化，那么上面的解决方案将很好地工作。

但是，如果需要更新的任何字段包含序列化的数据，那么转储文件上的SQL查询或简单的搜索/替换将破坏序列化(除非替换的字符串与搜索的字符串具有完全相同的字符数)。

可以肯定的是，a &;serialized&;Field看起来是这样的:

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

相关数据中的字符数被编码为数据的一部分。 序列化是一种转换“对象”的方法。转换成易于存储在数据库中的格式，或易于在不同语言之间传输对象数据。 下面是用来序列化对象数据的不同的方法的解释，以及为什么你可能想这样做，这里是一个以wordpress为中心的帖子

如果MySQL有一些内置的工具来自动处理序列化数据，那就太神奇了，但它没有，而且由于存在不同的序列化格式，它这样做甚至没有意义。

< >强wp-cli 上面的一些答案似乎是针对WordPress数据库的，因为它序列化了很多数据。WordPress提供了一个命令行工具wp search-replace， 做可以处理序列化。 基本命令是:

    wp search-replace 'an-old-string' 'a-new-string' --dry-run
然而，WordPress强调guid不应该被改变，所以它建议跳过该列。 它还表明，通常情况下你会想要跳过wp_users表。 下面是它的样子:

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

注意:我添加了--dry-run标志，所以复制粘贴不会自动破坏任何人的数据库。在您确定脚本执行了您想要的操作之后，再次运行它，不要使用该标志。

< >强插件 如果你正在使用WordPress，也有许多免费和商业插件提供gui界面来做同样的事情，打包了许多额外的功能

Interconnect/it PHP脚本 Interconnect/它提供了一个PHP脚本来处理序列化数据:安全搜索和替换工具。它是为在WordPress网站上使用而创建的，但它看起来可以用于任何由PHP序列化的数据库。 许多公司，包括WordPress自己，都推荐这个工具。指令在这里，大约3/4页向下

小开

phpMyAdmin包含一个简洁的查找和替换工具。

选择表，然后点击搜索 > 寻找并替换

这个查询花了大约一分钟的时间，成功地用post_content列中的newurl.ext替换了数千个oldurl.ext实例

截图of the find-and-replace feature in phpMyAdmin

这个方法最棒的地方是:你可以在提交之前检查每个匹配。

注意:我使用phpMyAdmin 4.9.0.1

小开

对于大小写字母组成的句子， 我们可以使用BINARY REPACE

UPDATE `table_1`  SET  `field_1` =  BINARY REPLACE(`field_1`, 'find_string', 'replace_string')
小开 
UPDATE  `MySQL_Table`
SET  `MySQL_Table_Column` = REPLACE(`MySQL_Table_Column`, 'oldString', 'newString')
WHERE  `MySQL_Table_Column` LIKE 'oldString%';
小开

当在phpmyadmin中进行搜索和替换时，我有很好的运气:

更新tableName SET fieldName1 = 'foo' WHERE fieldName1 = 'bar';

当然，这一次只适用于一个表。

小开

这是一个如何在数据库中查找和替换的例子

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

TABLE_NAME =比;用你的表名更改它

COLUMN =比;将其更改为您的列，确保它存在


提交答案