Question 2020 in Wordpress Question posted in
The official Wordpress documentation can be found here.

MySQL query to get table name with numerically highest number – WordPress

I have a WordPress multisite database, which has a lot of orphan tables I need to get rid of. The names are structured like this. The number in the table name is the site ID.

wp_9892_wc_booking_relationships
wp_10001_wc_booking_relationships
wp_18992_wc_deposits_payment_plans
wp_20003_followup_coupons
wp_245633_followup_coupon_logs

I want to make a query to find out the highest site ID from the table names.

I’ve tried queries like this

SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
AND table_name REGEXP '^wp_[0-9]+_[a-z0-9]+'
ORDER BY table_name DESC
LIMIT 1;

But that sorts the results in an unexpected way: I get

wp_9_woocommerce_log

When with LIMIT 10 I see there are names with higher numbers:

wp_99_woocommerce_log
wp_999_woocommerce_log
wp_999_wc_webhooks
wp_999_wc_download_log
wp_999_wcpv_per_product_shipping_rules
wp_999_wcpv_commissions
wp_9999_wcpv_per_product_shipping_rules
wp_9999_wcpv_commissions
wp_9998_wc_points_rewards_user_points_log

Is this something that’s doable with a SQL query?

Tags:

2

Answers


  1. 0

    Consider the following data example.

    CREATE TABLE test(
table_name  varchar(255) );

insert into test values
('wp_99_woocommerce_log'),
('wp_999_woocommerce_log'),
('wp_999_wc_webhooks'),
('wp_999_wc_download_log'),
('wp_999_wcpv_per_product_shipping_rules'),
('wp_999_wcpv_commissions'),
('wp_9999_wcpv_per_product_shipping_rules'),
('wp_9999_wcpv_commissions'),
('wp_9998_wc_points_rewards_user_points_log');

    Using,

    SELECT table_name 
FROM test
order by (substring_index(substring_index(table_name, 'wp_', -1), '_', 1) * 1 )  desc ;

    Will give the following result:

    table_name
wp_9999_wcpv_per_product_shipping_rules
wp_9999_wcpv_commissions
wp_9998_wc_points_rewards_user_points_log
wp_999_woocommerce_log
wp_999_wc_webhooks
wp_999_wc_download_log
wp_999_wcpv_per_product_shipping_rules
wp_999_wcpv_commissions
wp_99_woocommerce_log

    https://dbfiddle.uk/590L44Xr

    Using substring_index twice we get the number between wp_ and the second _.

    * 1 is a shortcut to cast the varchar to int.

    In your case it will be something like

    SELECT table_name 
FROM information_schema.tables
WHERE table_type = 'base table'
AND table_name REGEXP '^wp_[0-9]+_[a-z0-9]+'
ORDER BY  (substring_index(substring_index(table_name, 'wp_', -1), '_', 1) * 1 )   DESC
LIMIT 1;
    Login or Signup to reply.
  2. 0

    One way is to extryct the numbers and sort it

    Example

    CREATE TABLE table1
    (`tb` varchar(34))
;
    
INSERT INTO table1
    (`tb`)
VALUES
    ('wp_9892_wc_booking_relationships'),
    ('wp_10001_wc_booking_relationships'),
    ('wp_18992_wc_deposits_payment_plans'),
    ('wp_20003_followup_coupons'),
    ('wp_245633_followup_coupon_logs')
;


    Records: 5  Duplicates: 0  Warnings: 0

    SELECT tb FROM table1 ORDER BY REGEXP_SUBSTR(tb,"[0-9]+") + 0  DESC LIMIT 1
    tb
    wp_245633_followup_coupon_logs

    fiddle

    So your query will look like

    SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
AND table_name REGEXP '^wp_[0-9]+_[a-z0-9]+'
ORDER BY REGEXP_SUBSTR(table_name,"[0-9]+") + 0  DESC
LIMIT 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search