防止重复输入的自动增量 [英] 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屋!