如何在两个表中最快地插入行？

Question

我有两个mysql表格，我想在它们两个中都插入一个电子邮件。

我需要将电子邮件拆分为名称和域名，并将它们的每个部分插入到一个表中。

我的域名表格是这样的：

+----+--------+
| id | domain | primary(id)
+----+--------+

我的email表格如下：

+-------+----------+
| eMail | domainId | primary key(eMail, domainId)
+-------+----------+

即：[e-mail] > example.com 必须插入到 domain 表中，并且必须将 info 添加到带有 domainId 的 email 表中。

域名必须是唯一的。

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));
}

但是当我想添加大量电子邮件时，这种方式非常慢：

有没有更快的方法或者我可以用单个查询来完成这个任务..？

- Pooya

---

只需插入电子邮件并添加触发器，该表将提取域名并自动插入“domains”。 - juergen d

然后有更好的方法。但是如何添加触发器？ - Pooya

有多少电子邮件和域名？是否有很多来自相同域的电子邮件？ - FuzzyTree

是的，大多数都来自同一领域。但并不总是这样。 - Pooya

Answer 1

只需插入电子邮件地址，并添加一个触发器到“email”表中，该触发器会提取域名并自动将其插入到“domain”中。

DELIMITER |
CREATE TRIGGER insert_domain_trigger BEFORE INSERT ON email 
FOR EACH ROW 
begin
  set @domainname = substring(NEW.email, instr(NEW.email, '@') + 1);
  if(select count(*) from domain where domain = @domainname) = 0
  then
     insert into domain (domain) values (@domainname);
  end if;
end
|

- juergen d

Answer 2

由于您有很多来自同一域的电子邮件，您可以尝试将域ID缓存在本地PHP变量中，以减少对数据库的查询次数。

此外，您应该在循环之外只准备一次$insert和$name语句。

$domains = array();

foreach($dbh->query("SELECT id, domain FROM domain") as $domain) {
    $domains[$domain['domain']] = $domain['id'];
}

$insert = $dbh->prepare("INSERT INTO domain (domain) VALUES (:domain)");
$name = $dbh->prepare("INSERT INTO email (eMail, domainId) VALUES (:eMail,:domainId)");

foreach($emails as $email)
{
    list($account,$hostname) = explode('@',$email,2);
    $hostname = trim($hostname);

    if(!isset($domains[$hostname])) {
        $insert->execute(array(':domain'=>$hostname));
        $id = $dbh->lastInsertId();
        $domains[$hostname] = $id;
    }
    else {
        $id = $domains[$hostname];
    }    

    $name->execute(array(':eMail'=>trim($account),':domainId'=>$id));
}

- FuzzyTree

Answer 3

也许一个简单的步骤可以解决这个问题？

这需要数据库中的字段域为UNIQUE类型，以使用INSERT IGNORE INTO语法。

DELIMITER $$

CREATE PROCEDURE `sp_InsertEmail`(IN p_Email VARCHAR(100))
BEGIN

-- Declare variables
DECLARE v_Email VARCHAR(100);
DECLARE v_Domain VARCHAR(100);

-- Set the variables
SET v_Email = SUBSTRING(p_Email,1,INSTR(p_email,'@')-1);
SET v_Domain =SUBSTRING(p_Email,INSTR(p_Email,'@'));

-- Now insert ignore into domain table

INSERT IGNORE INTO db.domain (domain) VALUES(v_Domain);

-- Then insert like this.

INSERT IGNORE INTO db.email SELECT v_Email,DomainId FROM domain WHERE domain=v_Domain;

END

然后只需调用该过程。

CALL db.sp_InsertEmail('[e-mail]')

- Mad Dog Tannen