Question

我有一张表格user 。它有列 id 和 email。

使用表

id | email
1  | [email protected]
2  | [email protected]

The id is a PRIMARY KEY AUTO_INCREMENT and the email is an UNIQUE KEY. When I insert a new row in the table and there is a DUPLICATE KEY exception thrown. I want to fetch the id on which the DUPLICATE KEY exception was thrown.

现在,我正在做这个...

BEGIN
DECLARE EXIT HANDLER FOR 1062
    BEGIN
        SELECT id
        INTO id
        FROM user
        WHERE email =  [email protected] ;
    END;
    INSERT INTO user
    (email)
    VALUES
    ( [email protected] );

SELECT LAST_INSERT_ID() INTO id;
END;

我想知道是否有更好的办法做到这一点。这就是为了避免再次扫描表格,获取它已经扫描过的身份,以检查电子邮件的独特性。

Answer 1

使用 < a href=" "http://dev.mysql.com/doc/refman/5.1/en/ Indeplain-on-dupreplicate.html" rel="nofollow"\ code >INSTERT... 在 DUPLICATE KEY UPDATE 上,然后 < a href=""http://dev.mysql.com/doc/en/getting-unique-id.html" rel="nofolfollow" 使自动加速的id和往常一样 :

如果一个表格包含 AUTO_INCRONT 专栏和 < a href="http://dev.mysql.com/doc/refman/5.1/en/ information-supids.html#dev.mysql.doc/doc/refrdex_dref="http://dev. mysql.com/code/code/rfman/5.1/en/formation-supid_comids.html_supid_l-adex-id > rel="Nofol="LA\code>LST_INSERT_ID_ID_ID() 。
INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

Answer 2

在通过 UNIQUE KEY 扫描时,使用了 UNIQUE KEY ,因此扫描速度相当快。



您不想在额外的 select  查询中自己检查是否有价值存在吗?

友情链接