MySQL chained REPLACE?

Question

UPDATE media 
WHERE id = 63212 AND description LIKE '%https://images.weserv.nl/%' 
SET description = REPLACE(description, 'https://images.weserv.nl/?url=http', 'http') 
SET description = REPLACE(description, '[img]https://blu.xyz/favicon.ico', '[img=35]https://blu.xyz/favicon.ico') 
SET description = REPLACE(description, '[img]http', '[img=400]http') 
SET description 
SET description = REPLACE(description, 'https://images.weserv.nl/?url=', 'https://') SET description = REPLACE(description, '&w=35', '') 
SET description = REPLACE(description, '&w=400', '') 
SET description = REPLACE(description, '&h=400', '') 
SET description = REPLACE(description, '&w=300', '') 
SET description = REPLACE(description, '&h=300', '');

我得到以下返回:

ERROR 1064(42000):你的SQL语法有错误;检查与MySQL服务器版本对应的手册，以便在'WHERE id = 63212 AND description LIKE '%https://images.weserv附近使用正确的语法。nl/%' SET description '

谁能给我一些指导?

感谢

Answer 1

From MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/update.html

WHERE语句应该在SET语句之后，并且只允许1x SET语句出现。

:

UPDATE media 
SET description = REPLACE(description, 'https://images.weserv.nl/?url=http', 'http') 
description = REPLACE(description, '[img]https://blu.xyz/favicon.ico', '[img=35]https://blu.xyz/favicon.ico'), 
description = REPLACE(description, '[img]http', '[img=400]http'), 
description = REPLACE(description, 'https://images.weserv.nl/?url=', 'https://'),
description = REPLACE(description, '&w=35', ''),
description = REPLACE(description, '&w=400', ''), 
description = REPLACE(description, '&h=400', ''), 
description = REPLACE(description, '&w=300', ''), 
description = REPLACE(description, '&h=300', '')
WHERE id = 63212 AND description LIKE '%https://images.weserv.nl/%' ;

Answer 2

你有很多问题。

首先，正如其他人注意到的，WHERE子句在SET子句之后。

第二，不能使用多个SET子句。如果您想在一个列上执行多个替换，则必须嵌套它们。我将展示如何为3个替换做，您可以将其扩展到所有替换。

UPDATE media
SET description = 
REPLACE(
REPLACE(
REPLACE(description, 'https://images.weserv.nl/?url=http', 'http'), 
'[img]https://blu.xyz/favicon.ico', '[img=35]https://blu.xyz/favicon.ico'),
'[img]http', '[img=400]http')
WHERE id = 63212 AND description LIKE '%https://images.weserv.nl/%' 

请注意，当您有这样的嵌套替换时，它们是从最内到外的处理。因此，如果存在重叠的搜索字符串，则将最具体的字符串嵌套得更深。