INSERT and return ID, or if DUPLICATE KEY return existing ID in MySQL

Question

I have a "tags" table, which has a UNIQUE on the "tags" column, to prevent duplicates.

I have a "bookmarks_tags" table, which relates bookmarks to tags, and it has a UNIQUE on the "bookmark_id" and "tag_id" columns, to prevent duplicates.

However, I need people to be able to add the same tag, and to accomplish this, I need some means of retrieving the ID of the existing tag to use as reference in the "bookmarks_tags" table.

Is there a way of writing an INSERT so that if it detects a duplicate, it returns the ID of that duplicate? Or, alternatively, would an INSERT ... SELECT be more appropriate for "bookmarks_tags" table?

The key thing here is that it has to work under both conditions; add as new, or retrieve old.

Also, LAST_INSERT_ID() is useless in this scenario, as the tag in question could have been added at any time.

Any ideas?

[this](http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) will help you — matzone, May 04 '13 at 13:32
I can write a SELECT query, but that isn't the problem. I need to know if an INSERT can accomplish the same thing, saving the extra coding; I'm using a framework, so there's several files that would need changing. — Wayne Smallman, May 04 '13 at 13:36
Possible duplicate of [MySQL ON DUPLICATE KEY - last insert id?](https://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id) — Lorenz Meyer, Jun 14 '17 at 08:54

score 23 · Answer 1 · edited Feb 23 '18 at 23:16

23

$query = "INSERT INTO table SET unique1=value1 AND unique2=value2… ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)";
$insert_result = mysql_query($query);
$id = mysql_insert_id();
return $id;

Insert Into Database or Return ID of Duplicate Row in MySQL (Backup link @WebArchive)

edited Feb 23 '18 at 23:16

Enissay

4,969
3
29
56

answered May 28 '13 at 16:19

Pham Quang

331
1
5

Is there a way to distinguish a new and a duplicate returned id? – Maciek Semik Aug 09 '17 at 21:47
1

Broken link. :( – Jared Dunham Aug 25 '17 at 18:30
1

@JaredDunham backup link added ;-) – Enissay Feb 23 '18 at 23:13
1

Do note this will cause autoincrements to increment – DAG Aug 22 '20 at 02:04

score 6 · Accepted Answer · answered May 04 '13 at 15:23

6

One way to do this is using INSERT IGNORE:

INSERT IGNORE INTO tags (tags,...) VALUES (the_new_tags, ...);

SELECT tag_id FROM tags WHERE tags=the_new_tags;

answered May 04 '13 at 15:23

Ross Smith II

11,799
1
38
43

Okay, that's the way I'm going. It's the one with the least hassle. Ross, thanks. – Wayne Smallman May 04 '13 at 20:35
Wouldn't @Pham Duc Quang method be better? Only requires one query. – Chris Oct 29 '13 at 15:06
1

Yes, `INSERT INTO tags SET tags=the_new_tags ON DUPLICATE KEY UPDATE tag_id=LAST_INSERT_ID(tag_id)` should work better, but I haven't tested it. – Ross Smith II Nov 04 '13 at 17:55
`Insert IGNORE` is a *bad idea* since it will ignore all errors (type, length etc.), instead is `INSERT ON DUPLICATE KEY UPDATE key=key` but will correctly throw other errors. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html See other answer: https://stackoverflow.com/a/16796885/3343553 – EdL May 27 '20 at 13:12

INSERT and return ID, or if DUPLICATE KEY return existing ID in MySQL

2 Answers2