mysql 记录不存在返回,mysql：如果不存在则插入记录，否则返回记录的id

i need to insert unique values in a table, and need the ids of records, need to insert those id's in relationship table,

need the query to insert the record if not exists return the inset id, if exists return the record primary key(id).

and i want to do this for multiple values, like orange, mango, banana, like batch insert.

schema:

------------

id | tag |

------------

1 | orange|

------------

i have trid this for a single record

INSERT INTO `tags` (`tag`)

SELECT 'myvalue1'

FROM tags

WHERE NOT EXISTS (SELECT 1 FROM `tags` WHERE `tag`='myvalue1')

LIMIT 1

posted the question to figure out some optimized solution, i don't want to use extra loops in the code to match the values from db.

解决方案

There is an example on the documentation page for INSERT ... ON DUPLICATE KEY UPDATE:

Your query would look like this:

INSERT INTO `tags` (`tag`) VALUES ('myvalue1')

ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), `tag`='myvalue1';

SELECT LAST_INSERT_ID();