代码之家 › 专栏 › 技术社区 › Alec

使用MySQL从URL到组获取域

mysql

Alec · 技术社区 · 16 年前

http://foo.com http://bar.foo.com http://foo.com/bar 但是我只对域名本身感兴趣,所以在这个例子中:foo.com。我想做的是选择这个表中域名存在的次数。比如:

SELECT "whatever the domain is in field 'url'", COUNT(*) AS count
FROM table_with_urls
GROUP BY "whatever the domain is in field 'url'"

关于堆栈溢出,也有一些类似的问题,但没有什么真正的答案。我不能使用LIKE或与REGEXP匹配,因为我并不(总是)寻找要匹配的特定域名,但大多数情况下我只是想要 全部的 表中的域名以及总计数。

3 回复 | 直到 16 年前

AJ. 16 年前

为“域”添加另一个索引列,并在插入时单独存储此值。

amir 14 年前

select SUBSTRING(url from 1 for locate('/',url ,10)-1),count(*) from url_list group by SUBSTRING(url from 1 for locate('/',url ,10)-1);

Brian Leishman 7 年前

如果您想安装MySQL扩展,那么 https://github.com/StirlingMarketingGroup/mysql-get-etld-p1

它基本上提取了你所期望的内容

select`get_etld_p1`('http://a.very.complex-domain.co.uk:8080/foo/bar');-- 'complex-domain.co.uk'
select`get_etld_p1`('https://www.bbc.co.uk/');-- 'bbc.co.uk'
select`get_etld_p1`('https://github.com/StirlingMarketingGroup/');-- 'github.com'
select`get_etld_p1`('https://localhost:10000/index');-- 'localhost'
select`get_etld_p1`('android-app://com.google.android.gm');-- 'com.google.android.gm'
select`get_etld_p1`('example.test.domain.com');-- 'domain.com'
select`get_etld_p1`('postgres://user:pass@host.com:5432/path?k=v#f');-- 'host.com'
select`get_etld_p1`('exzvk.omsk.so-ups.ru');-- 'so-ups.ru'
select`get_etld_p1`('http://10.64.3.5/data_check/index.php?r=index/rawdatacheck');-- '10.64.3.5'
select`get_etld_p1`('not a domain');-- null

CREATE TABLE `db`.`sometablewithurls` (
  `SomeTableWithURLsID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `URL` TEXT NOT NULL DEFAULT '',
  `_ETLDP1` VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`SomeTableWithURLsID`),
  INDEX `_ETLDP1` (`_ETLDP1` ASC));
DROP TRIGGER IF EXISTS `db`.`sometablewithurls_BEFORE_INSERT`;

DELIMITER $$
USE `db`$$
CREATE DEFINER = CURRENT_USER TRIGGER `db`.`sometablewithurls_BEFORE_INSERT` BEFORE INSERT ON `sometablewithurls` FOR EACH ROW
BEGIN

set new.`_ETLDP1`=ifnull(`get_etld_p1`(new.`URL`),'');

END$$
DELIMITER ;
DROP TRIGGER IF EXISTS `db`.`sometablewithurls_BEFORE_UPDATE`;

DELIMITER $$
USE `db`$$
CREATE DEFINER = CURRENT_USER TRIGGER `db`.`sometablewithurls_BEFORE_UPDATE` BEFORE UPDATE ON `sometablewithurls` FOR EACH ROW
BEGIN

set new.`_ETLDP1`=ifnull(`get_etld_p1`(new.`URL`),'');

END$$
DELIMITER ;

请注意页面上的索引 _ETLDP1 和