MySQL 如何搜索和替换数据库中字符串的所有实例？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/836083/
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
How to search and replace all instances of a string within a database?
提问by barfoon
I have a string that is contained inside of a wordpress install (the name of a server) thousands of times, across multiple columns, records and tables.
我有一个字符串，它包含在 wordpress 安装（服务器的名称）中数千次，跨越多个列、记录和表。
I'd like to update it with the location of another server - we are moving the content over.
我想用另一台服务器的位置更新它 - 我们正在移动内容。
So the source would be something like http://my-server1/some/link/to/something, and I'd want to replace it with http://my-other-server/some/link/to/something. I'm essentially looking to repeat this process for every instance of http://my-server1.
所以源将类似于http://my-server1/some/link/to/something，我想用http://my-other-server/some/link/to/something替换它。我基本上希望为http://my-server1 的每个实例重复这个过程。
Is there an easy way to do this in MySQL? A tool? Or do I sadly have to update every record problematically?
在 MySQL 中有没有一种简单的方法可以做到这一点？一个工具？或者我是否必须有问题地更新每条记录？
Thank you,
谢谢，
回答by cherouvim
A crude (but effective) way of doing it would be to dump the schema into a file, carefully apply the search-and-replace and then re-import.
一种粗略（但有效）的方法是将模式转储到文件中，仔细应用搜索和替换，然后重新导入。
As a matter of fact I did that today :)
事实上，我今天做到了:)
回答by Enbee
Came across this in a google search, but this may help some people. If you know the tables and columns (you could find this using the wildcard search in phpMyAdmin),
在谷歌搜索中遇到了这个，但这可能对某些人有所帮助。如果您知道表和列（您可以使用 phpMyAdmin 中的通配符搜索找到它），
UPDATE table_nameSET column_name= REPLACE(column_name, 'http://oldsite.com','http://newsite.com');
UPDATE table_nameSET column_name= REPLACE( column_name, ' http://oldsite.com', ' http://newsite.com');
Replace boldparts with your own.
用您自己的替换粗体部分。
If you had a large database you could apply this into a script that could loop through each table and column.
如果您有一个大型数据库，您可以将其应用到可以遍历每个表和列的脚本中。
回答by Code Synthesis
The MySQL dump method would be the best bet if you're happy to re-import the whole database. For anyone that doesn't want to do this - WordPress core installation only actually consists of 11 tables, of which few are content columns, so doing a replace by column would be equally easy. Assuming you don't have loads of plugin tables referencing your link or string this would be your SQL:
如果您愿意重新导入整个数据库，那么 MySQL 转储方法将是最好的选择。对于任何不想这样做的人 - WordPress 核心安装实际上只包含 11 个表，其中很少有内容列，因此按列替换也同样容易。假设您没有大量引用您的链接或字符串的插件表，这将是您的 SQL：
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');
回答by Luca Borrione
Your question has been posted on 2009 and during that year another guy developed a basic php tool to search for a string throughout all the tables of a certain database. Optionally you can also replace all its occurrences with a different string.
您的问题已于 2009 年发布，在那一年，另一个人开发了一个基本的 php 工具，用于在某个数据库的所有表中搜索字符串。或者，您还可以用不同的字符串替换所有出现的内容。
Still after 5 years (at time of writing) the use of this tool against a Wordpress installation is effective and much easier in my opinion than using a mysql dump (although you might want to create a dump as well before running the script for backup purposes).
仍然在 5 年后（在撰写本文时），在我看来，对 Wordpress 安装使用此工具比使用 mysql 转储更有效且更容易（尽管您可能还想在运行脚本以进行备份之前创建转储） ）。
You can find more info in the blog of its author Eric Amundsonand in the launchpad's page of the project MySQL Search & Replace
您可以在其作者Eric Amundson的博客和MySQL Search & Replace项目的启动板页面中找到更多信息
I don't believe you are still looking for an answer after all this time, but I decided to post my hint as well, hoping it can help someone else popping over here in the future still searching for a solution to this issue.
我不相信你这么长时间仍在寻找答案，但我决定也发布我的提示，希望它可以帮助其他人在未来仍然在这里寻找解决方案。
回答by markratledge
There is also a Wordpress plugin called Search Regex, which allows running grepsearch and replace across the database.
还有一个名为Search Regex的 Wordpress 插件，它允许grep在整个数据库中运行搜索和替换。
回答by Alexey Vorobyov
Addittionally to Brad Larson's answer - to set variables like:
除了布拉德·拉森的回答 - 设置变量，如：
SET @what_to_be_replaced = "what_to_be_replaced", @to_be_replaced_by = "to_be_replaced_by";
And then use it as follows:
然后按如下方式使用它：
UPDATE wp_commentmeta SET meta_value = REPLACE(meta_value, @what_to_be_replaced, @to_be_replaced_by );
回答by Ondrej
MySQL's REPLACE or replacing the string manually in sql file or db in WordPressis not a good way. WordPress uses serialized fields that may break if you replace some string in them (that was my case)
MySQL 的 REPLACE 或手动替换 sql 文件中的字符串或WordPress 中的db不是一个好方法。WordPress 使用序列化字段，如果您替换其中的某些字符串，这些字段可能会中断（这就是我的情况）
I used Better Search Replaceplugin that worked for me perfectly
我使用了非常适合我的Better Search Replace插件
