违反唯一键时获取行的ID [英] Get the id of a row when UNIQUE KEY is violated

问题描述

我有一张桌子user.它具有列id和email.

I have a table user. It has columns id and email.

用户表

id | email
1  | [e-mail]
2  | [e-mail]

id是PRIMARY KEY AUTO_INCREMENT，email是UNIQUE KEY. 当我在表中插入新行并且抛出DUPLICATE KEY异常时.我想获取引发DUPLICATE KEY异常的id.

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.

现在我正在执行此操作-

Right now I am doing this -

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

SELECT LAST_INSERT_ID() INTO id;
END;

我想知道是否有更好的方法可以做到这一点.那是为了避免再次扫描表格以获得已经扫描过的ID以检查电子邮件的唯一性.

I want to know if there is a better way to do this. That is to avoid scanning the table again to get the id for which it had already scanned to check the uniqueness of the email.

推荐答案

使用 INSERT ... ON DUPLICATE KEY UPDATE ，然后获取自动递增照常输入ID :

如果表包含AUTO_INCREMENT列和 INSERT ... ON DUPLICATE KEY UPDATE 插入或更新一行， LAST_INSERT_ID() 函数返回AUTO_INCREMENT值.例外:有关更新， LAST_INSERT_ID() 在MySQL 5.1.12之前没有意义.但是，您可以使用 LAST_INSERT_ID(expr) .假设id是AUTO_INCREMENT列.使 LAST_INSERT_ID() 对于更新有意义，请按如下所示插入行:

If a table contains an AUTO_INCREMENT column and INSERT ... ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. Exception: For updates, LAST_INSERT_ID() is not meaningful prior to MySQL 5.1.12. However, you can work around this by using LAST_INSERT_ID(expr). Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

这篇关于违反唯一键时获取行的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！