SQL数据库批量修改网站中的图片地址
     2020-3-6    +1°    53  

由于网站的种种原因,当需要对wordpress中的自定义字段进行批量修改的时候,由于大量的字段内容需要修改或者删除,我们不太可能用手工进行修改,这也不太现实,所以今天分享一下wordpress 批量替换、删除字段内容的方法,以供大家参考。

我想大家都知道如何批量替换wordpress文章图片地址,假设你换了域名。

UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ws4.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ws4.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://wx4.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://wx4.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'http://ww4.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww1.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww2.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww3.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww4.sinaimg.cn/mw690', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww1.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww2.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww3.sinaimg.cn/large', 'http://btnose.com');
UPDATE wp_posts SET post_content = REPLACE( post_content, 'https://ww4.sinaimg.cn/large', 'http://btnose.com');

那么自定义字段内容该如何替换呢?这里假设你创建了一个自定义字段名字为bimg用于连接缩略图地址。

例如原来自定义字段bimg的图片地址为 http://老域名/images/goodjob.jpg。

如果你想替换掉老域名可以这么写:

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'bimg';

同理:

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'small';

同理:

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://ws4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'http://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/mw690', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx1.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx2.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx3.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';

UPDATE wp_postmeta SET
meta_value = replace(meta_value, 'https://wx4.sinaimg.cn/large', 'http://btnose.com')
WHERE meta_key = 'pic';