搜索并替换字段中的一些文本

What MySQL query will do a text search and replace in one particular field in a table?

什么MySQL查询将执行文本搜索并替换表中的一个特定字段?

I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

例如，搜索foo并用bar替换，因此一个带值hello foo的字段记录成为hello bar。

7 个解决方案

#1

414

Change table_name and field to match your table name and field in question:

更改table_name和字段，以匹配所涉及的表名和字段:

UPDATE table_name SET field = REPLACE(field, 'foo', 'bar') WHERE INSTR(field, 'foo') > 0;

REPLACE (string functions)
替换(字符串函数)
INSTR (string functions)
INSTR(字符串函数)

#2

UPDATE table_name 
SET field = replace(field, 'string-to-find', 'string-that-will-replace-it');

#3

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.

把这个放在这里，这样其他人就能立刻找到它。

#4

 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');

#5

The Replace string function will do that.

替换字符串函数会这样做。

#6

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.

我使用上面的命令行如下:更新表名set字段= replace(字段，'和'，'和');其目的是替换和使用And(“A”应该小写)。问题是它无法在数据库中找到“And”，但是如果我使用like“%和%”，那么它就可以找到它和许多其他的作为单词一部分的和，甚至是已经是小写的。

#7

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 WHERE clause altogether is slowest, even if the column is not indexed.

INSTR()方法是第二快的方法，并且省略了WHERE子句是最慢的，即使列没有被索引。

#1

414