MySQL 從 URL 欄位提取域名三層結構：子域、頂級域、根域

需求背景

表中 dm 欄位儲存完整 URL，為了模糊查詢與統計效能，新增三欄預計算：

• sdm (subdomain)：完整主機名，如 123456.user.qzone.qq.com
• tdm (topdomain)：頂級域，如 qq.com
• rdm (rootdomain)：根域後綴，如 com

DDL 新增欄位

ALTER TABLE `tablename`
  ADD `sdm` VARCHAR(64) NOT NULL,
  ADD `tdm` VARCHAR(32) NOT NULL,
  ADD `rdm` VARCHAR(8) NOT NULL;

四步驟更新資料

1. 提取主機名（去除 scheme、path、port）

UPDATE `tablename`
SET `sdm` = SUBSTRING_INDEX(
    SUBSTRING_INDEX(SUBSTRING_INDEX(`dm`, '/', 3), '/', -1), ':', 1);

邏輯：取第 3 個 / 前的子串（含 scheme://host:port），再取最後一段，再去除 port。

2. 提取根域後綴

UPDATE `tablename`
SET `rdm` = (
    IF(`sdm` REGEXP '(com|tel|mobi|net|org|asia|me|tv|biz|cc|name|info)(\\.[a-z]{2})$',
        SUBSTRING_INDEX(`sdm`, '.', -2),
        IF(`sdm` REGEXP '([a-z]{2,4})$',
            SUBSTRING_INDEX(`sdm`, '.', -1),
            ''
        )
    )
);

支援常見頂級域（含雙段如 .co.uk 邏輯可自行擴充）。

3. 由子域與根域反推頂級域

UPDATE `tablename`
SET `tdm` = CONCAT(
    SUBSTRING_INDEX(TRIM(TRAILING `rdm` FROM `sdm`), '.', -2),
    `rdm`
);

邏輯：從 sdm 移除尾部 rdm，取剩餘部分最後兩段，再拼回 rdm。

驗證結果

SELECT dm, sdm, tdm, rdm FROM tablename LIMIT 10;

影響列數：126,978（示例）

效能與維護建議

• 三欄加索引：INDEX idx_sdm (sdm), INDEX idx_tdm (tdm), INDEX idx_rdm (rdm)
• 觸發器自動維護：BEFORE INSERT/UPDATE 呼叫相同邏輯
• 定期抽樣驗證 tdm = SUBSTRING_INDEX(sdm, '.', -2) ... 邏輯一致性