In MySQL, extract the domain name information from the URL in the field

If you have a field DM records a URL, in order to better optimize the fuzzy query speed or statistical speed, the original structure of the data table added 3 fields, respectively

' SDM ' varchar () not NULL, #subdomain记录子域名, such as: 123456.user.qzone.qq.com
' TDM ' varchar not NULL, #topdomain记录一级域名, such as: qq.com
' RDM ' varchar (8) Not NULL, #rootdomain记录根域, such as: com

Order of Operations

# 1, Add fields for table
ALTER TABLE ' tablename ' add ' SDM ' varchar NOT NULL, add ' TDM ' varchar is not NULL, add ' RDM ' varchar (8) is not null;

# 2, the extraction URL in the domain name information

UPDATE ' tablename ' SET ' SDM ' =substring_index (substring_index substring_index (' DM ', '/', 3), '/',-1), ': ', 1 ';

# 3, extraction domain name in the root domain
UPDATE ' tablename ' SET ' rdm ' = (IF (' SDM ' REGEXP ') (comtelmobinetorgasiametvbizccnameinfo) (\\.[ A-ZA-Z]{2}) $ ', Substring_index (' SDM ', '. ',-2), IF (' SDM ' REGEXP ' ([a-za-z]{2,4}) $ ', Substring_index (' SDM ', '. ',-1), ') ));

# 4, according to the sub domain name and root domain to take a level of domain name

UPDATE ' tablename ' SET ' TDM ' =concat (Substring_index (TRIM (trailing ' RDM ' from ' SDM '), '. ',-2), ' RDM ');

Execution results:
Effects of data bars: 126978
Time: 7.172ms