SQL -替换列值没有按预期工作

Question

最初网站是用wordpress构建的。域从https://olddomain.com更改为https://newdomain.com，我也必须迁移到DB。

在Post内容中有图像url，如

• https://olddomain.com/wp-content/upload/2023/01/imagename.png
等
• https://olddomain.com/wp-content/upload/xxxx/xx/imagename.png。

我想实现的是用https://newdomain.com/public/media/images/imagename.png代替https://olddomain.com/wp-content/upload/2023/01/imagename.png

我尝试使用REPLACE通过sql查询如下:

Update  posts
Set post_description = replace(post_description, 'https://olddomain.com/wp-content/upload/', 'https://newdomain.com/public/media/images/') 

https://olddomain.com/wp-content/upload/xxxx/xx/imagename.png变成了https://newdomain.com/public/media/images/xxx/xx/imagename.png

现在的挑战是用"代替/xxxx/xx。是否有可能用SQL替换或有什么替代解决方案。

谢谢!

Answer 1

试试这个mysql查询:

UPDATE posts
SET post_description = REPLACE(
REPLACE(
post_description, 
'https://olddomain.com/wp-content/upload/', 
'https://newdomain.com/public/media/images/'
), 
SUBSTRING(post_description, LOCATE('https://olddomain.com/wp-content/upload/', post_description) + LENGTH('https://olddomain.com/wp-content/upload/'), 8),
''
)

Answer 2

下面是一个应该工作的示例查询:

UPDATE posts
SET post_description = REPLACE(post_description,
'https://olddomain.com/wp-content/uploads/',
'https://newdomain.com/public/media/images/')
, post_description = CONCAT(SUBSTRING_INDEX(post_description, '/', -3),
'/',
SUBSTRING_INDEX(post_description, '/', -1))
WHERE post_description LIKE '%https://olddomain.com/wp-content/uploads/%';