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

Source: Internet
Author: User
Tags mysql domain domain name sub domain
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



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.