MySQL 更新列值，替换部分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10177208/
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
Update a column value, replacing part of a string
提问by Addev
I have a table with the following columns in a MySQL database
我在 MySQL 数据库中有一个包含以下列的表
[id, url]
And the urls are like:
网址是这样的：
http://domain1.com/images/img1.jpg
I want to update all the urls to another domain
我想将所有网址更新到另一个域
http://domain2.com/otherfolder/img1.jpg
keeping the name of the file as is.
保持文件名不变。
What's the query must I run?
我必须运行什么查询？
回答by Dmytro Shevchenko
UPDATE urls
SET url = REPLACE(url, 'domain1.com/images/', 'domain2.com/otherfolder/')
回答by Marc B
UPDATE yourtable
SET url = REPLACE(url, 'http://domain1.com/images/', 'http://domain2.com/otherfolder/')
WHERE url LIKE ('http://domain1.com/images/%');
relevant docs: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace
相关文档：http: //dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_replace
回答by schellack
Try using the REPLACE function:
尝试使用REPLACE 函数：
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
Note that it is case sensitive.
请注意，它区分大小写。
回答by Kenneth Daly
You need the WHEREclause to replace ONLYthe records that complies with the condition in the WHERE clause (as opposed to all records). You use %sign to indicate partial string: I.E.
您需要WHERE子句来仅替换符合 WHERE 子句中条件的记录（而不是所有记录）。您使用%符号表示部分字符串：IE
LIKE ('...//domain1.com/images/%');
means all records that BEGINwith "...//domain1.com/images/"and have anything AFTER (that's the %for...)
意味着所有记录BEGIN与"...//domain1.com/images/"和有什么AFTER（这是%为...）
Another example:
另一个例子：
LIKE ('%http://domain1.com/images/%')
which means all records that contains "http://domain1.com/images/"
这意味着包含的所有记录 "http://domain1.com/images/"
in any part of the string...
在字符串的任何部分...
回答by ManiMaran A
Try this...
尝试这个...
update [table_name] set [field_name] =
replace([field_name],'[string_to_find]','[string_to_replace]');
回答by TechyFlick
First, have to check
首先，必须检查
SELECT * FROM universityWHERE course_name LIKE '%&%'
SELECT * FROM universityWHERE course_name LIKE '%&%'
Next, have to update
接下来，必须更新
UPDATE university SET course_name = REPLACE(course_name, '&', '&') WHERE id = 1
更新大学 SET course_name = REPLACE(course_name, '&', '&') WHERE id = 1
Results:Engineering & Technology => Engineering & Technology
结果：工程与技术 =>工程与技术
