我有一个非常大的mysql表存储域和子域。它的创建语法如下
CREATE TABLE `domain` (
`domain_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`domain_name` varchar(255) COLLATE utf8_turkish_ci NOT NULL,
PRIMARY KEY (`domain_id`),
UNIQUE KEY `dn` (`domain_name`),
KEY `ind_domain_name` (`domain_name`)
) ENGINE=InnoDB AUTO_INCREMENT=78364364 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;
，它存储像
这样的值domain_id | domain_name
1 | a.example.com
2 | b.example.com
3 | example.com
4 | facebook.com
5 | a.facebook.com
6 | google.com
我想找到任何顶级域的子域，然后我将子域与其"父域"匹配。
例如a.example.com和b.example.com是example.com的子域，所以在我的新列上命名为parent_domain_id，我将设置example.com的domain_id。(如果domain是顶级域，它的parent_domain_id将为0)
我用PHP和mysql工作，我的机器有8GB的RAM，所以我有一些设备限制。用PHP逐行检查大数据集有什么技巧吗?
编辑:对于大多数域名这应该工作。
你可以得到一个包含所有域(固定长度以内)的排序列表，该列表按域和长度递增排序。
我想从@RyanVincent的评论开始。
select domain_id, domain_name, reverse(domain_name) as reversed
from domain
order by
rpad(reverse(domain_name),130,' '),
length(domain_name),
reverse(domain_name),
domain_id
目标是能够按长度排序，然后按字母排序。
你的例子将给出
google.com -> moc.elgoog,
a.google.com -> moc.elgoog.a
xy.a.google.com -> moc.elgoog.a.yx
b.google.com -> moc.elgoog.a
$currentDomains = array();
/*
array domainpart => id
moc => 0
elgoog => id of google.com as long as we are in subdomains of google.com
a => id of a.google.com as long as we are in subdomains of a.google.com
this gets never longer then the number of domainparts, so usually a very
short array!
*/
$sql = "select domain_id, domain_name, reverse(domain_name) as reversed'n"
. " from domain 'n"
. " order by 'n"
. " rpad(reverse(domain_name),130,' '), 'n"
. " length(domain_name), 'n"
. " reverse(domain_name), 'n"
. " domain_id"
;
doSelect($sql);
while($row = getRow()){
$parts = preg_split('/'./', $row["reversed"]);
# print("parts = 'n");print_r($parts);
$rid = $row["domain_id"];
$matchedDomains = array();
$parentId = $rid; // if no parent, show to yourself
$i = 0;
// 1. match identical parts
// php is funny, the array is a key=>value but with
// foreach it restores the key-values in the inserted order.
foreach($currentDomains as $name => $cid){
# print("> check $i '$name' '{$parts[$i]}''n");
if($parts[$i] == $name){
$matchedDomains[$name] = $cid;
if($cid > 0){
$parentId = $cid;
}
$i++;
}
else{
break;
}
}
// 2.
// new parts
while ($i < count($parts)-1){
# print("> store '{$parts[$i]}' 'n");
$matchedDomains[$parts[$i]] = 0; // no row matches those
$i++;
}
$matchedDomains[$parts[count($parts)-1]] = $rid;
$currentDomains = $matchedDomains;
print(" update domain set parent_id = $parentId where id = $rid'n"); // use PDO
}
所以google.com是它自己的父域名，a.google.com的父域名是google.com等等