Massive Tags Remove Using MySQL

by

MySql DELETE syntax is not very different fiom SELECT, so you can delete from multiple tables using a single query.

Taxonomies informations in WordPress are in 3 tables:

  • wp_terms
  • wp_term_taxonomy
  • wp_term_relationships

the first 2 contain term / taxonomy informations, the 3rd contains association between terms and posts.

The query to delete all tags from WordPress db is:

DELETE t, tr, tt
FROM wp_terms t  
INNER JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tt.taxonomy = 'post_tag'

Note that the prefix wp_ can be different in your setup, it depends on $table_prefix setting on your wp_config.php file. More info here.

Be sure to replace the prefix with correct value in the query for all the 3 tables.

Also note that on multisite install, the full table name is

$prefix . $blogid . '_' . $tablename`

e.g. if the prefix is the default 'wp_' the blog is 2, the 3 tables will be named:

  • wp_2_terms
  • wp_2_term_relationships
  • wp_2_term_taxonomy

More info here.

Finally, please, be sure to have a full, valid database backup before running such a query.

The query can be ran using a database admin tool like phpMyAdmin.

Running query via php is simple in one side, because using WordPress $wpdb class, table names are auto-resolved, but is a harder in another side, because you have to prevent the query run multiple times, you have to check user capabilities and you need to edit php files.

Following code use a transient to ensure that function run once, and also run it only when a super admin is logged in backend using is_super_admin().

add_action('admin_init', function() {
  if ( ! is_super_admin() ) return;
  if ( get_transient('tags_cleaned') ) return;
  add_action( 'shutdown', 'my_clean_tags' );
});

function my_clean_tags() {
  if ( ! is_super_admin() ) return;
  if ( get_transient('tags_cleaned') ) return;
  remove_action( current_filter(), __FUNCTION__ );
  global $wpdb;
  $q = "
  DELETE t, tr, tt
  FROM {$wpdb->terms} t  
  INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
  INNER JOIN {$wpdb->term_relationships} tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
  WHERE tt.taxonomy = 'post_tag'
  ";
  $result = $wpdb->query( $q );
  set_transient(' tags_cleaned', $result );
  return $result;
}

Leave a Comment