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.”

ADVERTISEMENTS

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.

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
0 comments… add one

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.