做项目的时候发现需要清除文章内容的图片和统一替换为其它图片,想到了一种比较好用的方法,备忘一下,同时也给遇到类似问题的同学做下参考
-- 清理图片
UPDATE pyadmin_article SET content=INSERT
(
content,
INSTR( content, '<img' ),
LOCATE ( '/>', content, INSTR( content, '<img' ) + 1 ) - INSTR( content, '<img' ) + 2,
''
),pic1='',pic2='' WHERE hid in (17537,14000) AND INSTR(content, '<img') > 0;
-- 替换图片
UPDATE pyadmin_article SET content=INSERT
(
content,
INSTR( content, '<img' ),
LOCATE ( '/>', content, INSTR( content, '<img' ) + 1 ) - INSTR( content, '<img' ) + 2,
'<img alt="" src="https://www.xxx.net/test.jpg" style="height:320px; width:838px" />'
),pic1='',pic2='' WHERE hid = 4557 AND INSTR(content, '<img') > 0;