如何在MySQL中搜索不含TLD的域名主体部分？

我来给你几个实用的方案，根据你的域名结构和MySQL版本选最合适的就行：

1. 简单场景：单后缀域名（如testdomain.com） #

如果你的域名都是单后缀格式（只有一个.分隔主体和TLD），用SUBSTRING_INDEX函数最直接高效，它能提取第一个.之前的所有内容作为域名主体：

SELECT * FROM domains 
WHERE SUBSTRING_INDEX(domain_name, '.', 1) LIKE '%test%';

比如对于testdomain.com，这个函数会返回testdomain，搜索test能命中，搜索com则不会匹配到任何结果，完美符合你的需求。

2. 复杂场景：多级域名/多部分TLD（如sub.testdomain.co.uk） #

如果你的域名包含多级子域名或者多部分TLD（比如.co.uk、.com.cn），可以用MySQL 8.0+支持的REGEXP_SUBSTR函数，精准提取最后一个TLD之前的所有内容（或者结合已知的TLD列表来排除）：

方法A：提取最后一个.之前的内容 #

适合不确定TLD类型，但只想去掉最后一段后缀的场景：

SELECT * FROM domains 
WHERE REGEXP_SUBSTR(domain_name, '^(.*)\\.[^.]+$', 1, 1, 'e', 1) LIKE '%test%';

这个正则会捕获最后一个.之前的所有字符作为主体，比如sub.testdomain.co.uk会提取出sub.testdomain.co，如果你的需求是只保留二级域名（testdomain），那需要结合TLD列表来处理。

方法B：结合已知TLD列表精准排除 #

如果你能列出所有需要排除的TLD（比如.com、.co.uk、.org），可以用正则匹配这些TLD并提取主体：

SELECT * FROM domains 
WHERE domain_name REGEXP '^(.*)(\\.(com|co\\.uk|org))$'
  AND REGEXP_SUBSTR(domain_name, '^(.*)(\\.(com|co\\.uk|org))$', 1, 1, 'e', 1) LIKE '%test%';

这样testdomain.co.uk会提取出testdomain，sub.testdomain.com会提取出sub.testdomain，完全符合你只搜索主体的需求。

3. 性能最优方案：预存域名主体字段 #

如果你的数据量很大，每次查询都计算主体部分会影响性能，建议在表中新增一个domain_main字段，提前存储域名主体：

-- 1. 新增字段
ALTER TABLE domains ADD COLUMN domain_main VARCHAR(255) COMMENT '域名主体（不含TLD）';

-- 2. 初始化现有数据（根据你的域名结构选择上面的方法计算主体）
UPDATE domains 
SET domain_main = SUBSTRING_INDEX(domain_name, '.', 1); -- 单后缀场景
-- 或者用正则方法初始化：
-- UPDATE domains SET domain_main = REGEXP_SUBSTR(domain_name, '^(.*)\\.[^.]+$', 1, 1, 'e', 1);

-- 3. 添加触发器自动维护（可选，确保新增/更新域名时自动同步主体）
DELIMITER //
CREATE TRIGGER update_domain_main BEFORE INSERT ON domains
FOR EACH ROW
BEGIN
  SET NEW.domain_main = SUBSTRING_INDEX(NEW.domain_name, '.', 1);
END //
DELIMITER ;

-- 4. 查询时直接搜预存字段
SELECT * FROM domains WHERE domain_main LIKE '%test%';

这个方案的查询性能是最好的，尤其适合高频搜索的场景。

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