CREATE DEFINER=`root`@`%` FUNCTION `fn_get_topDomainName_from_url`(`Article_URL_In` varchar(1000)) RETURNS varchar(200) CHARSET utf8
BEGIN
/***********************************************************
根据URL获取顶级域名
************************************************************/
declare domain_name_out varchar(200);
declare domain_info varchar(200);
declare root_domain_name varchar(200);
-- 获取域名信息
SET domain_info=SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(Article_URL_In, '/', 3), '/', -1) ,':',1);
-- 提取域名中的根域
SET root_domain_name=(IF(domain_info REGEXP '(com|tel|mobi|net|org|asia|me|tv|biz|cc|name|info|gov|cn|co|edu|europa)(\\.[a-zA-Z]{2})$',
SUBSTRING_INDEX(domain_info, '.', -2),
IF(domain_info REGEXP '([a-zA-Z]{2,4})$', SUBSTRING_INDEX(domain_info, '.', -1), '')));
-- 根据子域名和根域取一级域名
SET domain_name_out=CONCAT(SUBSTRING_INDEX(TRIM(TRAILING root_domain_name FROM domain_info), '.', -2), root_domain_name);
return domain_name_Out;
END