MySQL如何去重提取不含子域名的独立域名？

处理domain.co.uk这类带多级公共后缀的域名时，单纯用SUBSTRING_INDEX按固定段数截取会失效，以下是可行方案及最佳实践：

1. 基于公共后缀列表的标准方案（推荐） #

这是行业通用的最佳实践，核心是利用公共后缀列表（如Mozilla维护的后缀清单）匹配最长的公共后缀，再提取完整主域名。

步骤： #

• 先创建一张public_suffixes表，导入最新的公共后缀数据（如co.uk、com.cn、net.au等所有多级后缀）。
• 通过关联查询匹配最长后缀，再截取主域名：

-- 第一步：从URL中提取纯域名
WITH domain_only AS (
    SELECT 
        REGEXP_SUBSTR(referrer_url, '://([^/]+)/?', 1, 1, NULL, 1) AS domain
    FROM referrers
),
-- 第二步：匹配当前域名对应的最长公共后缀
matched_suffix AS (
    SELECT 
        d.domain,
        MAX(s.suffix) AS longest_suffix
    FROM domain_only d
    JOIN public_suffixes s ON d.domain LIKE CONCAT('%.', s.suffix)
    GROUP BY d.domain
)
-- 第三步：提取最终主域名
SELECT 
    CASE 
        WHEN longest_suffix IS NOT NULL THEN REPLACE(d.domain, CONCAT('.', longest_suffix), '') || '.' || longest_suffix
        ELSE d.domain
    END AS root_domain
FROM domain_only d
LEFT JOIN matched_suffix ms ON d.domain = ms.domain;

这种方法能覆盖所有合法的多级后缀场景，不会因新后缀出现而失效。

2. 硬编码常见多级后缀（临时过渡方案） #

若不想维护公共后缀表，可针对常见的多级后缀做特殊判断，适合临时需求：

SELECT
    CASE
        -- 处理.co.uk/.org.uk等英国后缀
        WHEN domain LIKE '%.co.uk' OR domain LIKE '%.org.uk' THEN SUBSTRING_INDEX(domain, '.', -3)
        -- 处理.com.cn/.net.cn等中国后缀
        WHEN domain LIKE '%.com.cn' OR domain LIKE '%.net.cn' THEN SUBSTRING_INDEX(domain, '.', -3)
        -- 其他常规域名取最后两段
        ELSE SUBSTRING_INDEX(domain, '.', -2)
    END AS root_domain
FROM (
    -- 先提取纯域名
    SELECT REGEXP_SUBSTR(referrer_url, '://([^/]+)/?', 1, 1, NULL, 1) AS domain
    FROM referrers
) AS sub;

缺点是无法覆盖所有特殊后缀，仅适合已知场景的临时使用。

3. 封装自定义函数复用逻辑 #

将公共后缀匹配逻辑封装成SQL函数，方便日常调用：

DELIMITER //
CREATE FUNCTION get_root_domain(domain VARCHAR(255)) 
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE longest_suffix VARCHAR(255);
    -- 查询当前域名的最长公共后缀
    SELECT MAX(suffix) INTO longest_suffix
    FROM public_suffixes
    WHERE domain LIKE CONCAT('%.', suffix);
    
    IF longest_suffix IS NOT NULL THEN
        RETURN CONCAT(SUBSTRING_INDEX(domain, CONCAT('.', longest_suffix), 1), '.', longest_suffix);
    ELSE
        -- 无匹配时默认取最后两段
        RETURN SUBSTRING_INDEX(domain, '.', -2);
    END IF;
END //
DELIMITER ;

-- 调用函数提取主域名
SELECT get_root_domain(REGEXP_SUBSTR(referrer_url, '://([^/]+)/?', 1, 1, NULL, 1)) AS root_domain
FROM referrers;

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