MySQL 搜索并替换字段中的一些文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/125230/
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
MySQL search and replace some text in a field
提问by julz
What MySQL query will do a text search and replace in one particular field in a table?
什么 MySQL 查询将在表中的一个特定字段中进行文本搜索和替换？
I.e. search for fooand replace with barso a record with a field with the value hello foobecomes hello bar.
即搜索foo并替换为，bar因此带有值的字段的记录hello foo变为hello bar。
回答by Joe Skora
回答by thesmallprint
UPDATE table_name
SET field = replace(field, 'string-to-find', 'string-that-will-replace-it');
回答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 basdog22
And if you want to search and replace based on the value of another field you could do a CONCAT:
如果您想根据另一个字段的值进行搜索和替换，您可以执行 CONCAT：
update table_name set `field_name` = replace(`field_name`,'YOUR_OLD_STRING',CONCAT('NEW_STRING',`OTHER_FIELD_VALUE`,'AFTER_IF_NEEDED'));
Just to have this one here so that others will find it at once.
只是把这个放在这里，以便其他人立即找到它。
回答by Gaspy
In my experience, the fastest method is
根据我的经验，最快的方法是
UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE field LIKE '%foo%';
The INSTR()way is the second-fastest and omitting the WHEREclause altogether is slowest, even if the column is not indexed.
该INSTR()方法是第二快的和省略WHERE共条款是最慢的，即使列没有编入索引。
回答by Schwann
I used the above command line as follow: update TABLE-NAME set FIELD = replace(FIELD, 'And', 'and'); the purpose was to replace And with and ("A" should be lowercase). The problem is it cannot find the "And" in database, but if I use like "%And%" then it can find it along with many other ands that are part of a word or even the ones that are already lowercase.
我使用上面的命令行如下： update TABLE-NAME set FIELD = replace(FIELD, 'And', 'and'); 目的是用 and 替换 And（“A”应该是小写的）。问题是它在数据库中找不到“And”，但如果我使用“%And%”，那么它可以找到它以及作为单词一部分的许多其他and，甚至是已经小写的and。
