如何在MySQL中分组对比URL模式，统计含不同slug的相似路径数？

我正在执行一项内容分析任务，现有一个名为articles的数据表，其中url列存储完整URL。需要识别多个URL共享通用路径但仅最后一个slug不同的情况，例如：

https://metehanpicks.com/top-guide/best-cbd-gummies-carter  
https://metehanpicks.com/top-guide/best-cbd-gummies-guide

目标是编写MySQL查询实现以下功能：

• 提取最后一个连字符分隔部分之前的路径（比如上述示例的共享根路径为https://metehanpicks.com/top-guide/best-cbd-gummies）；
• 按该共享根路径对URL进行分组；
• 返回：共享根路径、关联的不同slug（最后一段）数量、该分组中所有完整URL的拼接列表。

当前尝试的查询仅适用于最后一个slug包含连字符的URL，在无连字符的slug、更深层级的路径等边缘场景下会失效：

SELECT 
  LEFT(url, LENGTH(url) - LENGTH(SUBSTRING_INDEX(url, '-', -1)) - 1) AS base_path,
  COUNT(*) AS variant_count,
  GROUP_CONCAT(url) AS variants
FROM articles
WHERE url LIKE 'https://metehanpicks.com/top-guide/best-cbd-gummies%'
GROUP BY base_path
HAVING COUNT(*) > 1;

可以结合正则表达式实现更健壮的逻辑，兼容各种边缘场景，以下是优化后的查询：

SELECT
  -- 提取最后一个连字符之前的共享根路径
  REGEXP_REPLACE(url, '-[^-]+$', '') AS base_path,
  -- 统计不同slug的数量（去重避免重复URL干扰）
  COUNT(DISTINCT SUBSTRING_INDEX(url, '-', -1)) AS variant_count,
  -- 拼接分组内的所有完整URL，用逗号分隔
  GROUP_CONCAT(DISTINCT url SEPARATOR ', ') AS variants
FROM articles
-- 可选：过滤目标路径范围，缩小查询范围
WHERE url LIKE 'https://metehanpicks.com/top-guide/best-cbd-gummies%'
GROUP BY base_path
-- 仅返回包含多个不同slug的分组
HAVING variant_count > 1;

核心逻辑说明 #

• REGEXP_REPLACE(url, '-[^-]+$', '')：正则表达式匹配最后一个连字符及其后的所有内容（-[^-]+$表示以连字符开头，后续为任意非连字符字符直到字符串末尾），替换为空后得到共享根路径。该逻辑兼容：
  • 最后一个slug无连字符的情况（如https://example.com/path/single-slug，提取后为https://example.com/path）；
  • 更深层级路径的情况（如https://example.com/level1/level2/parent-slug-child，提取后为https://example.com/level1/level2/parent-slug）；
• COUNT(DISTINCT SUBSTRING_INDEX(url, '-', -1))：通过SUBSTRING_INDEX提取最后一个连字符后的slug，加上DISTINCT确保统计的是不同slug的数量，避免重复URL导致计数不准；
• GROUP_CONCAT(DISTINCT url SEPARATOR ', ')：拼接分组内不重复的完整URL，用逗号分隔便于查看所有变体。

扩展：处理带查询参数的URL #

如果URL中包含查询参数（如https://example.com/path/slug?param=1），可以先移除参数再处理，确保路径提取准确：

SELECT
  -- 先移除查询参数，再提取共享根路径
  REGEXP_REPLACE(
    IF(LOCATE('?', url) > 0, LEFT(url, LOCATE('?', url) - 1), url),
    '-[^-]+$',
    ''
  ) AS base_path,
  COUNT(DISTINCT SUBSTRING_INDEX(
    IF(LOCATE('?', url) > 0, LEFT(url, LOCATE('?', url) - 1), url),
    '-',
    -1
  )) AS variant_count,
  GROUP_CONCAT(DISTINCT url SEPARATOR ', ') AS variants
FROM articles
WHERE url LIKE 'https://metehanpicks.com/top-guide/best-cbd-gummies%'
GROUP BY base_path
HAVING variant_count > 1;

该版本通过IF(LOCATE('?', url) > 0, LEFT(url, LOCATE('?', url) - 1), url)判断并移除查询参数部分，再进行后续的路径提取和统计。

内容的提问来源于stack exchange，提问作者MHD7 Music