MySQL SQL UPDATE 中的 str_replace？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3944000/
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
str_replace in SQL UPDATE?
提问by Julian H. Lam
Here's a sample table:
这是一个示例表：
name | picture
John S. | http://servera.host.com/johns.png
Linda B. | http://servera.host.com/lindab.png
...
Let's say there are several hundred more records.
假设还有数百条记录。
Let's also say we moved servers from "servera" to "serverb".
假设我们将服务器从“servera”移动到“serverb”。
Would it be possible to go into this table with one queryto rename the content in the column "picture" for every record to read the correct server name?
是否可以通过一个查询进入该表，为每条记录重命名“图片”列中的内容以读取正确的服务器名称？
回答by Anatoly G
T-SQL:
T-SQL：
update TBL
set picture = Replace(picture, 'servera', 'serverb')
where picture like '%servera%'
Oracle:
甲骨文：
update TBL
set picture = replace(picture, 'servera', 'serverb')
where picture like '%servera%'
MySQL:
MySQL：
update TBL
set picture = REPLACE(picture, 'servera', 'serverb')
where picture like '%servera%'
回答by Andrew
UPDATE users
SET picture = REPLACE(picture, 'http://servera.host.com/', 'http://serverb.host.com/')
WHERE picture LIKE 'http://servera.host.com/%';
I'm including more of the string because I'd worry about 'fixing' an image named 'somethingserverasomething.jpg'. I might also think about having a base_url table and just storing image file names in users, but that's not the question you asked ;-)
我包含了更多的字符串，因为我担心“修复”名为“somethingserverasomething.jpg”的图像。我可能还会考虑拥有一个 base_url 表并仅在用户中存储图像文件名，但这不是你问的问题;-)
