Deleting all existing comment author urls in WordPress using mysql command

last updated in Categories Command Line Hacks, Howto, Programming, Web Developer

I have like over 50k+ comments posted on this site. A commenter is allowed to leave their website or Github/Twitter URL in the comment section. Unfortunately, many websites are dead creating a mess. Many are just spam and nothing else. This is not good for anyone. Here is a quick way to delete all existing comment author URLs in WordPress using mysql command line option.

Make a backup before you make any changes. See my “Shell Script To Backup MySql Database Server.”

Finding dead links

This plugin will monitor your blog looking for broken links and let you know if any are found. It monitors links in your posts, pages, comments, the blogroll, and custom fields (optional). It produces nice summary as follows:

See a list of broken links that have been detected
See a list of broken links that have been detected

Using command line option to delete all comment author urls

Use the mysql command as follows :
$ mysql -u wp_user -h wp_db_host_here -p wp_blog_db
Where,

  • -u : The MySQL user name to use when connecting to the server.
  • -h : The MySQL host/server name to use when connecting to the server.
  • -p : Prompt for the password.
  • wp_blog_db : My WordPress DB name.

Once connected, get information about table structure or query execution plan using DESC or EXPLAIN sql command:
mysql> DESC wp_comments;
OR
MariaDB [wp_blog_db]> EXPLAIN wp_comments;
Sample outputs:

+----------------------+---------------------+------+-----+---------------------+----------------+
| Field                | Type                | Null | Key | Default             | Extra          |
+----------------------+---------------------+------+-----+---------------------+----------------+
| comment_ID           | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| comment_post_ID      | bigint(20) unsigned | NO   | MUL | 0                   |                |
| comment_author       | tinytext            | NO   |     | NULL                |                |
| comment_author_email | varchar(100)        | NO   | MUL |                     |                |
| comment_author_url   | varchar(200)        | NO   |     |                     |                |
| comment_author_IP    | varchar(100)        | NO   |     |                     |                |
| comment_date         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| comment_date_gmt     | datetime            | NO   | MUL | 0000-00-00 00:00:00 |                |
| comment_content      | text                | NO   |     | NULL                |                |
| comment_karma        | int(11)             | NO   |     | 0                   |                |
| comment_approved     | varchar(20)         | NO   | MUL | 1                   |                |
| comment_agent        | varchar(255)        | NO   |     |                     |                |
| comment_type         | varchar(20)         | NO   |     |                     |                |
| comment_parent       | bigint(20) unsigned | NO   | MUL | 0                   |                |
| user_id              | bigint(20) unsigned | NO   |     | 0                   |                |
+----------------------+---------------------+------+-----+---------------------+----------------+
15 rows in set (0.00 sec)

See wp_comments tables section for more info. I need to use comment_author_url. Just verify it:
MariaDB [wp_blog_db]> select comment_author_url from wp_comments;
To empty/zap all existing comments simply run:
MariaDB [wp_blog_db]> UPDATE wp_comments SET comment_author_url = '';
OR
MariaDB [wp_blog_db]> UPDATE wp_comments SET comment_author_url = '' WHERE NOT comment_author_email = 'webmaster@cyberciti.biz';
Sample outputs:

Query OK, 5679 rows affected (0.17 sec)
Rows matched: 22271  Changed: 5679  Warnings: 0

The plugin allows you to unlink all broken URLs but changing 5k broken URLs via web UI is just too much clicking. Hence, I wrote this quick post so that others can use it to avoid bad user experiences or to get better search engine ranking.

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.