Get the id of a row when UNIQUE KEY is violated

Question

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

USER TABLE

id | email
1  | xxx@gmail.com
2  | yyy@gmail.com

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 = 'xxx@gmail.com';
    END;
    INSERT INTO user
    (email)
    VALUES
    ('xxx@gmail.com');

SELECT LAST_INSERT_ID() INTO id;
END;

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.

score 1 · Answer 1 · answered May 24 '12 at 10:19

1

In scaning by UNIQUE KEY BTREE is used so it's quite fast.

Don't you want check for existing of value by yourself in additional select query

answered May 24 '12 at 10:19

triclosan

5,578
6
26
50

+1 Checking prior to the insert is a far better solution that trying to insert and catching the error. – GarethD May 24 '12 at 10:23
I want to know the id of the row where the UNIQUE KEY is being thrown. – JHS May 24 '12 at 10:23

score 0 · Accepted Answer · answered May 24 '12 at 11:26

0

Use INSERT ... ON DUPLICATE KEY UPDATE, then get the autoincremented id as usual:

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;

answered May 24 '12 at 11:26

eggyal

122,705
18
212
237

I do not want to update. I just want the id for which the email column gave a DUPLICATE KEY value. – JHS May 25 '12 at 07:21
@Juniad: So just do `INSERT ... ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)` as shown. Nothing will be changed, but `LAST_INSERT_ID()` will give you the ID you're after. – eggyal May 25 '12 at 08:54
Wrong. This will indeed update the `Auto_increment` value, as described here: http://stackoverflow.com/questions/12692003/avoid-increasing-auto-increment-value-when-using-last-insert-id – l33t Oct 02 '12 at 14:45
@NOPslider: I didn't realise that - thanks for the heads-up. Clearly, my comment "*nothing will be changed*" was over-zealous: as you point out, whilst no data will be changed, the next auto-increment value is updated. Sadly, I can no longer edit that comment, but this discussion should help to avoid any future misunderstanding. I do however feel the downvote (from whomever) is a bit OTT given that the above answer still fully and correctly addresses the OP's problem (which was about how one can obtain the ID of the relevant record, whether newly inserted or pre-existing). – eggyal Oct 02 '12 at 14:57
Sorry, that was an accidental click. Can't undo it :( – l33t Oct 02 '12 at 15:05

Get the id of a row when UNIQUE KEY is violated

2 Answers2

Linked

Related