Wordpress数据库常用的SQL语句合集

在PhpMyAdmin使用SQL语句来完成工作前，请务必备份数据库，以免出现失误。

1.数据库MyISAM转换InnoDB

ALTER TABLE wp_users ENGINE=INNODB;
ALTER TABLE wp_usermeta ENGINE=INNODB;
ALTER TABLE wp_term_taxonomy ENGINE=INNODB;
ALTER TABLE wp_term_relationships ENGINE=INNODB;
ALTER TABLE wp_terms ENGINE=INNODB;
ALTER TABLE wp_termmeta ENGINE=INNODB;
ALTER TABLE wp_posts ENGINE=INNODB;
ALTER TABLE wp_postmeta ENGINE=INNODB;
ALTER TABLE wp_options ENGINE=INNODB;
ALTER TABLE wp_links ENGINE=INNODB;
ALTER TABLE wp_comments ENGINE=INNODB;
ALTER TABLE wp_commentmeta ENGINE=INNODB;

2.替换语句

UPDATE wp_posts SET post_content = REPLACE( post_content, 'old content', 'new content' )
更新数据库中wp_posts表post_content字段的内容"old content"为"new content"(不包含双引号"")

UPDATE `wp_postmeta` SET `meta_value` = replace(`meta_value`, 'AAA', 'BBB')
更改文章自定义字段的名称

UPDATE wp_postmeta SET meta_value = '3' WHERE meta_value = '4' AND meta_key = 'down_price';
批量更新指定自定义字段的自定义字段值

3.删除语句

DELETE `wp_posts` FROM `wp_posts`,`wp_postmeta` WHERE `wp_posts`.ID = `wp_postmeta`.`post_id` AND `wp_postmeta`.`meta_key` = 'Test' AND `wp_postmeta`.`meta_value` LIKE '%ABCD%'
批量删除包含某个自定义字段指定内容的文章

DELETE ca_posts FROM ca_posts,ca_postmeta WHERE ca_posts.ID = ca_postmeta.post_id AND ca_postmeta.meta_key = 'Sold' AND length(ca_postmeta.meta_value) <3;
批量删除Sold字段内容长度小于3的文章

DELETE ca_posts FROM ca_posts,ca_postmeta WHERE ca_posts.ID = ca_postmeta.post_id AND ca_postmeta.meta_key = 'Sold' AND ca_postmeta.meta_value LIKE '%类型%';
批量删除Sold字段内容包含"类型"的文章

4.改变文章状态
UPDATE `wp_posts` SET `post_status` ='draft' WHERE (`post_status`='publish');
1.pending：待审 2.draft：草稿 3.auto-draft：自动保存的草稿 4.inherit：修订版本 5.trash：回收站 6.publish：已发布 7.future：定时 8.private：私有

UPDATE `wp_posts` SET `post_status` ='draft' WHERE `post_date` >='2020-05-01 00:00:00' and `post_date` < '2021-03-22 22:00:00:00';
将一个时间段的文章全部移动到草稿里

5.清理数据库
首先在wp-config.php中添加如下

define('MAGPIE_CACHE_ON', 0);//删除自动保存缓存（并关闭自动缓存功能）
define('WP_POST_REVISIONS', 0); //一个是删除RSS缓存（并关闭RSS缓存功能）

选择数据库，然后打开SQL命令
DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock';
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';//删除沉淀数据
DELETE FROM wp_posts WHERE post_status!='publish' OR post_type='revision';//清理自动保存草稿，历史版本，回收站
DELETE FROM wp_postmeta WHERE post_id NOT IN(SELECT ID FROM wp_posts); //清理因删除文件而仍然遗留的文章标签
TRUNCATE TABLE 'wp_commentmeta'；//清理防垃圾评论插件wp-commentmeta表
DELETE FROM wp_postmeta WHERE meta_key = '_edit_lock'; //清理wp_postmeta
DELETE FROM wp_postmeta WHERE meta_key = '_edit_last';
DELETE FROM wp_postmeta WHERE meta_key = '_revision-control';

WordPress 内置的工具修复和优化数据库

在网站的 wp-config.php 中添加

define('WP_ALLOW_REPAIR', true);

然后访问

http://你的域名/wp-admin/maint/repair.php