重复键上的MySQL ...获取现有ID? [英] MySQL on duplicate key... get existing ID?

问题描述

我正在使用ON DUPLICATE KEY UPDATE处理表上的重复插入，以便将其丢弃.

I'm using ON DUPLICATE KEY UPDATE to handle duplicate inserts on a table, in order that they are discarded.

就我而言，这是一个存储标签的简单表格:

In my case it's a simple table storing tags:

• id(int，PK，AI，未签名，不为null)
• 标签(varchar 25，不为null，唯一)

这工作正常，但是我需要检索ID-成功插入后插入ID，或者如果存在重复则返回现有ID.

This is working fine, but I need to retrieve the ID - either the insert ID, on successful insert, or the existing ID, if it's a duplicate.

我正在获取插入ID = 0，其中触发ON DUPLICATE KEY UPDATE，我猜这是预期的行为，因为没有发生插入.

I'm getting insert ID = 0 where ON DUPLICATE KEY UPDATE fires, which I guess is expected behaviour since no insert took place.

无论如何，我可以得到现有的ID，还是要前往一个单独的读取查询?

Is there anyway I can get the existing ID, or am I headed to a separate read query?

推荐答案

您可以添加第三列ModifiedDate并使用它:

You could add a third column ModifiedDate and use that:

insert into t(id, tag)
    select id, tag
    on duplicate key update ModifiedDate = now();

这将确保确实发生更新，并反过来确保LAST_INSERT_ID()返回一个值.

This will ensure that an update really occurs, and in turn, that LAST_INSERT_ID() returns a value.

这篇关于重复键上的MySQL ...获取现有ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！