如何在MySQL中查询不包含其他记录子串的所有域名

你可以直接使用以下SQL语句实现需求：

SELECT d1.name
FROM domains d1
WHERE NOT EXISTS (
    SELECT 1
    FROM domains d2
    WHERE d1.name <> d2.name
      AND d1.name LIKE CONCAT('%.', d2.name)
)

逻辑说明 #

• 核心通过NOT EXISTS判断当前域名是否存在更短的父域名记录：
  • 子域名的格式必然是[前缀].[父域名]，用LIKE CONCAT('%.', d2.name)做后缀匹配，既可以避免非域名段的错误子串匹配（比如不会把myexample.com错误识别为example.com的子域名），又能准确匹配所有层级的子域名
  • 加入d1.name <> d2.name条件排除自身匹配的情况

场景验证 #

完全符合你给出的两种预期：

1. 存在example.com记录时，所有三级、四级子域名都会匹配到更短的父域名被过滤，最终返回example.com、example.nl、example.org
2. 不存在example.com记录时，blah.example.com、www.example.com没有匹配到更短的父域名会被保留，而www.blah.example.com会匹配到blah.example.com被过滤

注意事项 #

该方案为临时方案，适合数据量不大的场景使用，如果你使用的是PostgreSQL数据库，拼接部分可以修改为d1.name LIKE '%.' || d2.name，逻辑完全一致。后续你新增父域名关联字段后，可以通过关联查询大幅提升查询性能。

内容的提问来源于stack exchange，提问作者Wim ten Brink