MySQL從URL字段提取域名三級結構：子域、主域、根域

目標結構設計

在原表新增三欄，優化模糊查詢與統計：

四步更新腳本

-- 1. 增加欄位
ALTER TABLE `tablename` 
ADD `sdm` varchar(64) NOT NULL,
ADD `tdm` varchar(32) NOT NULL,
ADD `rdm` varchar(8) NOT NULL;

-- 2. 提取子域名（去協議、取host、去端口）
UPDATE `tablename` 
SET `sdm` = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`dm`, '/', 3), '/', -1), ':', 1);

-- 3. 提取根域（針對常見雙字母/多字母頂級域）
UPDATE `tablename` 
SET `rdm` = IF(`sdm` REGEXP '(com|tel|mobi|net|org|asia|me|tv|biz|cc|name|info)(\\.[a-zA-Z]{2})$',
    SUBSTRING_INDEX(`sdm`, '.', -2),
    IF(`sdm` REGEXP '([a-zA-Z]{2,4})$',
        SUBSTRING_INDEX(`sdm`, '.', -1), ''));

-- 4. 由子域+根域反推主域
UPDATE `tablename` 
SET `tdm` = CONCAT(SUBSTRING_INDEX(TRIM(TRAILING `rdm` FROM `sdm`), '.', -2), `rdm`);

邏輯解析

• 步驟2：SUBSTRING_INDEX(dm, '/', 3)取https://host:port，再SUBSTRING_INDEX(..., '/', -1)取host:port，最後SUBSTRING_INDEX(..., ':', 1)去端口
• 步驟3：優先匹配xxx.co.uk類雙級頂級域（正則捕獲.co.uk），退而求次匹配單級頂級域（.com等）
• 步驟4：TRIM(TRAILING rdm FROM sdm)去掉尾部根域，再取倒數兩段拼回根域得主域

執行效果

實測126,978條記錄全量更新成功。查詢時直接WHERE tdm='qq.com'走索引，告別LIKE '%qq.com%'全表掃描。