防止重复输入的自动增量 [英] Prevent auto increment on duplicate entry

问题描述

我已经看到这个问题（见底部的链接），但我似乎无法弄清楚一个答案。问题是我将数据插入到具有主键的自动增量ID的表上，另一个字段具有UNIQUE索引以避免重复。这样做，但是当这种情况发生时，ID增加，尽管没有数据被存储。

I have seen this issue around (See links at bottom) but I can't seem to figure out an answer. The problem is that I insert data on a table with an auto increment ID that is a primary key, and another field with a UNIQUE index to avoid duplicates. This works, but when that happens the ID is incremented, although no data has been stored.

最好删除自动增量并处理它我自己选择最大（ID）？

目前我已经尝试了几种策略包括 INSERT IGNORE 和 INSERT ... ON DUPLICATE KEY UPDATE

At the moment I have tried several strategies to make it work as is, including INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE

我最近的尝试是使用以下查询：

My latest try was using the following query:

INSERT INTO
    content(field1, field2)
SELECT(:field1, :field2) FROM DUAL
WHERE NOT EXISTS(
    SELECT field1, field2
    FROM content
    WHERE field1 = :field1
)

---

相关

---

Related

• 在MySQL中，当存在重复条目错误，如何防止主键自动递增？


• SQL Server - 如何插入记录并确保它是唯一的




• In MySQL, when there is a "duplicate entry" error, how do I prevent the primary key from auto incrementing?
• SQL Server - How to insert a record and make sure it is unique

推荐答案

感谢这个问题我已经能够修复这个错误。问题是， SELECT（：field1，：field2）不应该有括号。所以查询应该是：

Thanks to this question I have been able to fix that error. The problem was that the SELECT(:field1, :field2) shouldn't have the parenthesis. So the query should be:

INSERT INTO
    content(field1, field2)
SELECT :field1, :field2 FROM DUAL
WHERE NOT EXISTS(
    SELECT field1, field2
    FROM content
    WHERE field1 = :field1
)

这篇关于防止重复输入的自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！