使用 MySQL 查询查找和替换整个表中的文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11839060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find and Replace text in the entire table using a MySQL query
提问by alyasabrina
Usually I use manual find to replace text in a MySQL database using phpmyadmin. I'm tired of it now, how can I run a query to find and replace a text with new text in the entire table in phpmyadmin?
通常我使用手动查找来替换使用 phpmyadmin 的 MySQL 数据库中的文本。我现在厌倦了，如何运行查询以在 phpmyadmin 的整个表中查找和替换文本？
Example: find keyword domain.com, replace with www.domain.com.
示例：查找关键字domain.com，替换为www.domain.com。
回答by swapnesh
For a single tableupdate
对于single table更新
UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')
From multiple tables-
来自multiple tables-
If you want to edit from all tables, best way is to take the dumpand then find/replaceand upload it back.
如果您想从所有表中进行编辑，最好的方法是使用dump然后find/replace上传回来。
回答by siliconrockstar
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.
我发现的最简单的方法是将数据库转储到文本文件，运行 sed 命令进行替换，然后将数据库重新加载回 MySQL。
All commands below are bash on Linux.
下面的所有命令都是 Linux 上的 bash。
Dump database to text file
将数据库转储到文本文件
mysqldump -u user -p databasename > ./db.sql
Run sed command to find/replace target string
运行 sed 命令来查找/替换目标字符串
sed -i 's/oldString/newString/g' ./db.sql
Reload the database into MySQL
将数据库重新加载到 MySQL
mysql -u user -p databasename < ./db.sql
Easy peasy.
十分简单。
回答by Lee Woodman
Put this in a php file and run it and it should do what you want it to do.
把它放在一个 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']);
}
回答by guest
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
在 PHPmyadmin 中运行 SQL 查询以查找和替换所有 wordpress 博客文章中的文本，例如查找 mysite.com/wordpress 并将其替换为 mysite.com/news 本示例中的表是 tj_posts
UPDATE `tj_posts`
SET `post_content` = replace(post_content, 'mysite.com/wordpress', 'mysite.com/news')
回答by Farid.O
Another option is to generate the statements for each column in the database:
另一种选择是为数据库中的每一列生成语句：
SELECT CONCAT(
'update ', table_name ,
' set ', column_name, ' = replace(', column_name,', ''www.oldDomain.com'', ''www.newDomain.com'');'
) AS statement
FROM information_schema.columns
WHERE table_schema = 'mySchema' AND table_name LIKE 'yourPrefix_%';
This should generate a list of update statements that you can then execute.
这应该会生成您可以执行的更新语句列表。
回答by Umesh Patil
UPDATE table SET field = replace(field, text_needs_to_be_replaced, text_required);
Like for example, if I want to replace all occurrences of John by Mark I will use below,
例如，如果我想用 Mark 替换所有出现的 John，我将在下面使用，
UPDATE student SET student_name = replace(student_name, 'John', 'Mark');
回答by llange
I believe "swapnesh" answer to be the best ! Unfortunately I couldn't execute it in phpMyAdmin (4.5.0.2) who although illogical (and tried several things) it kept saying that a new statement was found and that no delimiter was found…
我相信“swapnesh”答案是最好的！不幸的是，我无法在 phpMyAdmin (4.5.0.2) 中执行它，尽管不合逻辑（并尝试了几件事）它一直说找到了一个新语句并且没有找到分隔符......
Thus I came with the following solution that might be usefull if you exeprience the same issue and have no other access to the database than PMA…
因此，我提供了以下解决方案，如果您遇到相同的问题并且除了 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`;
To test the expected result you may want to use :
要测试您可能需要使用的预期结果：
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`;
回答by SherylHohman
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).
但是，如果需要更新的任何字段包含序列化数据，则 SQL 查询或转储文件上的简单搜索/替换将破坏序列化（除非替换的字符串与搜索的字符串具有完全相同的字符数）。
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 methodsused 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.
相关数据中的字符数被编码为数据的一部分。
序列化是一种将“对象”转换为易于存储在数据库中的格式，或在不同语言之间轻松传输对象数据的方法。
这是用于序列化对象数据的不同方法的解释，以及您可能想要这样做的原因，这是一篇以 WordPress 为中心的帖子：序列化数据，这意味着什么以及为什么它如此重要？用通俗的语言。
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.
如果 MySQL 有一些内置工具来自动处理序列化数据，那就太棒了，但它没有，而且由于有不同的序列化格式，所以这样做甚至没有意义。
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 doeshandle serialization.
A basic command would be:
wp-cli
上面的一些答案似乎特定于 WordPress 数据库，它序列化了其大部分数据。WordPress 提供了一个命令行工具wp search-replace，它可以处理序列化。
一个基本的命令是：
wp search-replace 'an-old-string' 'a-new-string' --dry-run
However, WordPress emphasizes that the guidshould never be changed, so it recommends skipping that column.
It also suggests that often times you'll want to skip the wp_userstable.
Here's what that would look like:
但是，WordPress 强调guid永远不应更改，因此建议跳过该列。
它还表明，您经常会想要跳过wp_users表格。
这就是它的样子：
wp search-replace 'https://old-domain.com' 'https://shiney-new-domain.com' --skip-columns=guid --skip-tables=wp_users --dry-run
wp search-replace 'https://old-domain.com' 'https://shiney-new-domain.com' --skip-columns=guid --skip-tables=wp_users --dry-run
Note: I added the --dry-runflag 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.
注意：我添加了--dry-run标志，因此复制粘贴不会自动破坏任何人的数据库。在您确定脚本执行您想要的操作后，请在没有该标志的情况下再次运行它。
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.
插件
如果您使用 WordPress，还有许多免费和商业插件可用，它们提供了一个 gui 界面来执行相同的操作，并打包了许多附加功能。
Interconnect/it php script
Interconnect/it offers a php script to handle serialized data: Safe Search and Replacetool. 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. Instructionshere, about 3/4 down the page.
Interconnect/it php 脚本
Interconnect/it 提供了一个 php 脚本来处理序列化数据：安全搜索和替换工具。它是为在 WordPress 网站上使用而创建的，但看起来它可以用于任何由 PHP 序列化的数据库。
许多公司，包括 WordPress 本身，都推荐这个工具。此处的说明，大约在页面下方的 3/4。
回答by youtag
phpMyAdmin includes a neat find-and-replace tool.
phpMyAdmin 包括一个简洁的查找和替换工具。
Select the table, then hit Search> Find and replace
选择表格，然后点击搜索>查找和替换
This query took about a minute and successfully replaced several thousand instances of oldurl.extwith the newurl.extwithin Column post_content
这个查询花费了大约一分钟，成功地取代的几千实例oldurl.ext与newurl.ext列内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
注意我正在使用 phpMyAdmin 4.9.0.1
回答by Omid Ahmadyani
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.
最好将其导出为 sql 文件，然后使用 Visual Studio 代码等编辑器打开它，然后查找并重新替换您的单词。我在 1 分钟内将 1 个 gig 文件 sql 替换为 16 个单词，总共 14600 个单词。它是最好的方法。替换后保存并再次导入。不要忘记用 zip 压缩它以进行导入。
