Deleting all existing comment author urls in WordPress using mysql command

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:

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 = '[email protected]';
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.

 

Source