SQL statements used in WordPress

Using wordpress to build a personal blog is more convenient. Sometimes it involves batch changes to the blog content. This article will share several valid SQL statements to facilitate content updates. Before making specific changes to database backup, we recommend that you back up the database. The phpMyAdmin backup procedure is as follows: logtailyourphpmyadmin. SelectyourWordPress

Using wordpress to build a personal blog is more convenient. Sometimes it involves batch changes to the blog content. This article will share several valid SQL statements to facilitate content updates. Before making specific changes to database backup, we recommend that you back up the database. The phpMyAdmin backup procedure is as follows: Login to your phpMyAdmin. Select your WordPress

Using wordpress to build a personal blog is more convenient. Sometimes it involves batch changes to the blog content. This article will share several valid SQL statements to facilitate content updates.

Database Backup

Before making specific changes, we recommend that you back up the database. The phpMyAdmin backup steps are as follows:

• Login to yourPhpMyAdmin.
• Select your WordPress database.
• Click onExportAt the top of the navigation.
• Select the tables you want to backup, or select all tables to backup the whole database.
• Select SQL to export. SQLExtension.
• Check the "Save as file" checkbox.
• Choose compression type, selectGzippedTo compress the database to a smaller size.
• Finally clickGo, And a download window will prompt you to save your backup database file.

Practical SQL statements

Change blog website Siteurl & Homeurl

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Change blog ownership GUID

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Change URL reference in blog

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Change image loading reference in blog

UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldsiteurl.com', 'src="http://yourcdn.newsiteurl.com');UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://yourcdn.newsiteurl.com') WHERE post_type = 'attachment';

Change URL reference in blog Meta information

UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');

Change the default Administrator "Admin" username

UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';

Password Reset

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';

Change the owner of A blog post (from author B to author)

First, you need to obtain the ID of the two authors. You can view the author's details through the Administrator Panel. In this case, you can view the link in the browser's address bar and find "user_id =? "

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

Delete blog revision records

DELETE a,b,c FROM wp_posts aLEFT 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'

Deletes the specified Meta information of a blog.

DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

Email Address for collecting comments

SELECT DISTINCT comment_author_email FROM wp_comments;

Delete Pingback of a blog

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Delete all spam comments

• 0 = Comment Awaiting Moderation
• 1 = Approved Comment
• Spam = Comment marked as Spam

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete unused Tags

SELECT * From wp_terms wtINNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

References

• 13 Useful WordPress SQL Queries You Wish You Knew Earlier

The post WordPress SQL statement appeared first on is silent.