如何用MySQL查询WordPress多站点数据库中的最大站点ID？

问题背景 #

我有一个WordPress多站点数据库，其中存在大量需要清理的孤立表，表名格式为wp_<站点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

其中的数字部分为站点ID。我尝试通过以下SQL查询获取最大站点ID：

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; 

但结果不符合预期，返回了wp_9_woocommerce_log，而实际存在站点ID更大的表（比如wp_9999_...）。

问题原因 #

按table_name字符串排序时，是逐个字符比较ASCII码的。wp_9_...中第三个字符是9，后续的_ASCII码小于9，所以字符串排序会把wp_9_...排在wp_99_...之前，导致错误结果。

解决方案 #

需要提取表名中的站点ID并转换为数值类型，再按数值排序或取最大值。以下是几种可行的SQL写法：

方法一：使用SUBSTRING_INDEX提取并转换（兼容所有MySQL版本） #

SELECT 
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(table_name, '_', 2), '_', -1) AS UNSIGNED) AS site_id
FROM information_schema.tables
WHERE table_type = 'base table'
  AND table_name REGEXP '^wp_[0-9]+_[a-z0-9_]+'
ORDER BY site_id DESC
LIMIT 1;

• 逻辑：先提取wp_<站点ID>部分，再从中分离出站点ID字符串，最后转为无符号整数，按数值降序取第一个结果。

方法二：使用REGEXP_SUBSTR提取（MySQL 8.0+支持） #

如果你的MySQL版本是8.0及以上，可直接用正则捕获站点ID：

SELECT 
  CAST(REGEXP_SUBSTR(table_name, 'wp_([0-9]+)_', 1, 1, 'c', 1) AS UNSIGNED) AS site_id
FROM information_schema.tables
WHERE table_type = 'base table'
  AND table_name REGEXP '^wp_[0-9]+_[a-z0-9_]+'
ORDER BY site_id DESC
LIMIT 1;

• 逻辑：通过正则表达式的捕获组直接提取wp_后的数字部分，转为数值后排序取最大。

方法三：直接获取最大站点ID（无需返回表名） #

如果只需要最大ID值，可使用MAX函数简化：

SELECT 
  MAX(CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(table_name, '_', 2), '_', -1) AS UNSIGNED)) AS max_site_id
FROM information_schema.tables
WHERE table_type = 'base table'
  AND table_name REGEXP '^wp_[0-9]+_[a-z0-9_]+';

以上三种方法都能正确获取表名中的最大站点ID。

内容的提问来源于Stack Exchange，提问作者And Finally