Have you ever encountered the following situations:

• When a blog changes its domain name, the content of the blog article must also be changed
• The image address used has been changed
• I have written a lot of articles, and I want to switch authors
• I want to delete all the messages of a certain hateful commenter
• Want to change the website URL of all comments by a commenter
• Want to disable pingback of all articles
• Want to disable the comment function of all articles

All of these , all involve one keyword: Batch modification. If you manually modify the content of articles one by one in the WordPress backend, I believe it will drive you crazy. For some blogs with thousands of articles, it is almost impossible to complete the task. This article will teach you how to use SQL statements to operate your database and achieve batch modification of data.

If you have never learned database-related knowledge, you definitely don’t know what SQL is, but the content covered in this article does not require you to understand database knowledge, nor does it require you to be proficient in writing SQL statements. You are completely You can directly use the SQL mentioned in this article. Below we will introduce the functions of each SQL statement in sections. All statements use the default wp_ table prefix. If yours is not, please change it yourself.

Before we begin, let’s first introduce how to execute SQL statements and perform batch operations. Nowadays, most spaces use phpmyadmin to manage databases. Here we will use phpmyadmin as an example to introduce how to execute SQL statements:

• Enter your phpmyadmin management page, and then enter your blog corresponding There is a

  SQL
option in the menu bar of the database

• . Click in

• and an input box for the SQL statement will appear. Now You can enter the SQL statement in it

• After inputting, click Execute, the SQL statement you just entered will be executed

• The SQL statement has been executed, and your articles have been modified in batches. Now go and see if all your articles have been changed.

• Finally, a reminder: the SQL introduced below The statements have been tested on my blog, but despite this, you must back up your database before operating the database; it is a good habit to back up your database regularly

SQL command execution Window:

1. Modify article content in batches:

If you want to replace what you have written before For certain content in all articles, such as changing the name of the blog, changing the URL of the blog, changing the link of the article image, etc., you can use the following SQL statement:

UPDATE wp_posts 
SET post_content = replace( post_content, '露兜博客', '露兜中文博客' );

This statement The function is to replace the words Pandan Blog in all articles with Pandan Chinese Blog . You can make some changes as needed. Because the article content is stored in the form of HTML code in the database, the above SQL statement can also replace the HTML code.

If you just want to change the link to the article illustration without affecting other links, you can use the following SQL statement. All src="oldurl.com is replaced with src="newurl.com

UPDATE wp_posts 
SET post_content = 
REPLACE (post_content, 'src="oldurl.com', 'src="newurl.com');

If you upload it as an image attachment, you need to change the GUID of the image attachment

UPDATE wp_posts
SET  guid = REPLACE (guid, 'oldsiteurl.com', 'newsiteurl.com') 
WHERE post_type = 'attachment';

2. Modify the article summary in batches:

The article summary is the content entered in the "Summary" box when you edit the article in the WordPress background. If you want to change the article summary in batches, you can use the following statement:

UPDATE wp_posts 
SET post_excerpt = replace( post_excerpt, '露兜博客', '露兜中文博客' );

The function of this statement is to replace all the words Pandan Blog in all article summaries with Pandan Chinese Blog.

3. Batch modify the authors of articles:

Suppose your blog has two registered users, Zhang San and Li Si, and you want to modify all Zhang San’s articles Under the leadership of Li Si, what should we do now? You can execute the following statement:

UPDATE wp_posts 
SET post_author = 李四用戶id 
WHERE post_author = 張三用戶id;

How to get the user ID of Li Si and the user ID of Zhang San? You can execute the following SQL statement:

SELECT ID, user_nicename, display_name FROM wp_users;

The IDs, nicknames and publicly displayed names of all registered users on your blog will be listed. You can now find the corresponding user ID, as shown below, the ID of zhangsan is 2, and the ID of lisi is 5:

Your SQL can be written like this:

UPDATE wp_posts 
SET post_author = 5 WHERE post_author = 2;

4、批量修改文章評(píng)論者的網(wǎng)站URL：

假設(shè)，你的博客有個(gè)非常忠實(shí)的讀者，給你的博客文章留下很多有用的評(píng)論，同時(shí)他的評(píng)論都填寫了留言者的網(wǎng)站URL，但是有一天他的博客域名換了，并請(qǐng)求你更新他留言中的網(wǎng)站URL，那你怎么辦？手動(dòng)一個(gè)一個(gè)幫他改，這不太現(xiàn)實(shí)。你可以使用以下SQL語句：

UPDATE wp_comments 
SET comment_author_url = REPLACE( comment_author_url, 'oldurl.com', 'newurl.com' )

以上語句，將留言者所有舊的網(wǎng)站鏈接oldurl.com，更改為新的網(wǎng)址newurl.com

5、禁用所有文章的pingback功能：

開啟pingback功能，可以在別人引用你的文章鏈接的情況下，給你發(fā)送通知，但是該功能似乎對(duì)我們的文章沒多大幫助，那為何不把pingback給禁止了呢？在WordPress后臺(tái) - 設(shè)置 - 討論，取消勾選"接收來自外部博客的引用通告(pingbacks 和 trackbacks)"，這樣以后的文章都不開啟pingback，但是該選項(xiàng)不會(huì)對(duì)之前的已發(fā)布的文章起作用，還是要用到SQL:

UPDATE wp_posts 
SET ping_status = 'closed';

6、刪除所有文章的修訂版：

在通常情況下，文章的修訂版對(duì)大多數(shù)人來說沒多大意義，而且修訂版的數(shù)量會(huì)隨著你修改文章的次數(shù)不斷增長，這會(huì)增加數(shù)據(jù)庫的查詢速度，這并不是什么好事?；ヂ?lián)網(wǎng)上有很多教你如何禁止修訂版的文章，還有很多插件可以刪除文章修訂版，你可以自己搜索看看。這里教你如何使用SQL語句，刪除所有已產(chǎn)生的文章修訂版數(shù)據(jù)：

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision';

7、刪除某個(gè)評(píng)論者的所有評(píng)論：

如果你的博客想要封殺某人，并刪除其在你博客的所有留言，可以使用以下SQL語句。

(1)根據(jù)留言者的博客URL進(jìn)行刪除，以下SQL語句將刪除所有URL為 www.example.com 的評(píng)論

DELETE from wp_comments 
WHERE comment_author_url LIKE '%www.example.com%';

(2)根據(jù)留言者的昵稱進(jìn)行刪除，以下語句將刪除所有昵稱為 example 的評(píng)論

DELETE from wp_comments 
WHERE comment_author = 'example';

(2)根據(jù)留言者的Email進(jìn)行刪除，以下語句將刪除所有Email為 example@example.com 的評(píng)論

DELETE from wp_comments 
WHERE comment_author_email = 'example@example.com';

8、替換所有評(píng)論中的敏感詞匯：

國內(nèi)的互聯(lián)網(wǎng)監(jiān)控力度表現(xiàn)出了不斷加強(qiáng)的趨勢，如果你的博客評(píng)論中出現(xiàn)了大量的敏感詞匯，很可能離被墻也不遠(yuǎn)了。最好的做法是，替換相關(guān)的敏感詞匯，以保證你的博客安全，以下SQL語句將所有評(píng)論中的 fuck，替換成 **，替換內(nèi)容根據(jù)你的需要來。

UPDATE wp_comments 
SET comment_content = replace( comment_content, 'fuck', '**' );

9、關(guān)閉文章評(píng)論功能

有時(shí)候你的博客可能會(huì)因?yàn)槟撤N原因，需要關(guān)閉文章的評(píng)論。在WordPress后臺(tái) - 設(shè)置 - 討論，那里取消勾選"允許人們發(fā)表新文章的評(píng)論"，以后發(fā)表的文章默認(rèn)是關(guān)閉評(píng)論的。但是之前已經(jīng)發(fā)表的文章，若想關(guān)閉評(píng)論需要你一篇一篇地去修改評(píng)論設(shè)置，這是一件比較痛苦的事情。以下SQL語句可以幫助你輕松地批量關(guān)閉文章評(píng)論：

(1) 關(guān)閉所有舊文章的評(píng)論：
通常情況下，一篇舊文章就很少會(huì)有人發(fā)表評(píng)論了，一般訪問舊文章的訪客大都來自搜索引擎，這是好事，但是這部分訪客還會(huì)提出一些新問題，尤其是技術(shù)問題，但是可能文章中提到的技術(shù)細(xì)節(jié)你已經(jīng)淡忘，這時(shí)候會(huì)讓你很難辦。最好的做法還是還是禁用舊文章的評(píng)論，以下SQL將禁止2009-01-01之前發(fā)表的所有文章的評(píng)論，你可以根據(jù)需要修改日期：

UPDATE wp_posts 
SET comment_status = &#39;closed&#39; WHERE post_date < &#39;2009-01-01&#39; AND post_status = &#39;publish&#39;;

(2) 關(guān)閉所有文章的評(píng)論：
有時(shí)候很不幸，在不可抗力的威脅下，你不得不關(guān)閉所有文章的評(píng)論，可以使用以下SQL語句：

UPDATE wp_posts 
SET comment_status = 'closed' WHERE post_status = 'publish';

推薦學(xué)習(xí)：《WordPress教程》