mysql - 更新路径行中域名的sql语句

Question

假设这张表

+--------------+---------------------------------+
| id           | path                            |
+--------------+---------------------------------+
| 1            | http://old.com/img/1.png        | 
| 2            | http://old.com/img/2.png        | 
| 3            | http://old.com/img/3.png        | 
| 4            | http://old.com/img/4.png        | 
| 5            | http://old.com/img/5.png        | 
| 6            | http://old.com/img/6.png        | 
+--------------+---------------------------------+

什么句子会更新成

+--------------+---------------------------------+
| id           | path                            |
+--------------+---------------------------------+
| 1            | http://new.com/img/1.png        | 
| 2            | http://new.com/img/2.png        | 
| 3            | http://new.com/img/3.png        | 
| 4            | http://new.com/img/4.png        | 
| 5            | http://new.com/img/5.png        | 
| 6            | http://new.com/img/6.png        | 
+--------------+---------------------------------+

我不知道如何在 mysql 中进行 str_replace

“UPDATE table SET path = REPLACE(path, 'old.com', 'new.com')” ??我不能在这里弄乱数据库..

Answer 1

update mytable set path = replace(path, 'old.com', 'new.com');

如果您担心更新的影响(您应该担心)，我首先这样做:

select path, replace(path, 'old.com', 'new.com') as new_path
from mytable;

我只是观察结果，看它们是否“看起来不错”。如果他们这样做，则触发更新。