我有两个
mysql表,我想在它们中插入一封电子邮件.
我需要将电子邮件拆分为名称和域名,并将它们的每个部分插入到一个表中.
我的域表是这样的:
+----+--------+
| id | domain | primary(id)
+----+--------+
我的电子邮件表是:
+-------+----------+
| eMail | domainId | primary key(eMail, domainId)
+-------+----------+
即:info@example.com> example.com必须在域表中插入
info必须在domainId的电子邮件表中添加.
域必须是唯一的.
我的尝试:
foreach($emails as $email)
{
list($account,$hostname) = explode('@',$email,2);
$query = $dbh->prepare("SELECT id FROM domain WHERE domain LIKE :domain LIMIT 0,1");
$query->execute(array(':domain'=>trim($hostname)));
if($query->rowCount())
{
$id = $query->fetch();
$id = $id['id'];
}else{
$insert = $dbh->prepare("INSERT INTO domain (domain) VALUES (:domain)");
$insert->execute(array(':domain'=>trim($hostname)));
$id = $dbh->lastInsertId();
}
$name = $dbh->prepare("INSERT INTO email (eMail, domainId) VALUES (:eMail, :domainId)");
$name->execute(array(':eMail'=>trim($account),':domainId'=>$id));
}
但是当我想添加批量电子邮件时,这种方式非常慢:
有没有更快的方法或者我可以在单个查询中执行此操作..?