0

Say, I've got a simple table: id as primary key, and field. Then i do: INSERT INTO table(id,field) VALUES (1,'blah') and then I do it again. So, how can I make MySQL return following:

id

1

So that it works like SELECT-ing conflicted key?

Community
  • 1
  • 1
No Way
  • 183
  • 9
  • Well, it just won't make you "do it again", so there will never be conflicting keys, as long as you've defined "id" as a primary key. Why do you need this? – Aioros Jan 08 '13 at 11:39
  • 4
    MySQL will return a duplicate-key error - how to analyze it is up to your language and API. – Max Yakimets Jan 08 '13 at 11:43

2 Answers2

0

I don't think that's possible without some code, mysql will return a generic duplicate key error

With-Code solution:

Are you using an AUTO_INCREMENT column ?

If you use INSERT IGNORE and the row is ignored,

the AUTO_INCREMENT counter is not incremented and LAST_INSERT_ID() returns 0,

which reflects that no row was inserted.

so:

  • INSERT IGNORE
  • if LAST_INSERT_ID(), then done (new row was inserted)
  • else your data is duplicate

last_insert_id() documentation >

insert Ignore documentation >

NicolaPasqui
  • 1,112
  • 8
  • 17
0

You might consider the following...

    DROP TABLE IF EXISTS my_table;

    CREATE TABLE my_table(my_id INT NOT NULL PRIMARY KEY,my_field VARCHAR(12) NOT NULL,conflict TINYINT NOT NULL DEFAULT 0);

    INSERT INTO my_table (my_id,my_field) VALUES (1,'blah') ON DUPLICATE KEY UPDATE conflict = 1;

    SELECT * FROM my_table WHERE conflict = 1;
    Empty set (0.00 sec)

    INSERT INTO my_table (my_id,my_field) VALUES (1,'blah') ON DUPLICATE KEY UPDATE conflict = 1;
    Query OK, 2 rows affected (0.07 sec)

    SELECT * FROM my_table WHERE conflict = 1;
    +-------+----------+----------+
    | my_id | my_field | conflict |
    +-------+----------+----------+
    |     1 | blah     |        1 |
    +-------+----------+----------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57