如何在重复密钥更新时禁止自动递增ID更新? [英] How can I disable an auto-incrementing ID from updating on duplicate key update?

问题描述

我认为这将很容易解决，但是我遇到了麻烦.我正在将记录插入具有自动递增ID的表中.在重复密钥更新时，我希望自动递增ID保持不变(而不是更新).例如，假设我有下表:

I thought this would be easy to solve but I'm having trouble. I'm inserting records into a table with an auto-incrementing ID. On duplicate key update, I would like the auto-incrementing ID to remain the same (not update). For example, let's say I have the following table:

+-------------+-----------+------+
| ID          | Fruit     | Color|
+-------------+-----------+------+
| 1           | Apple     | Red  | 
| 2           | Apple     | Green|
+-------------+-----------+------+

然后我跑:

INSERT INTO table (Fruit, Color) VALUES(
    Apple, Red)

由于我的唯一键是(Fruit，Color)(ID也是一个单独的键)，所以我希望ID列在表更新时保持原样(ID = 1).但是，当我插入上面的值时，ID会自动增加为3.

Since my unique key is (Fruit, Color) (also ID is a separate one), I want the ID column to remain exactly as it is (ID=1) when my table is updated; however, when I insert the values above, the ID auto-increments to 3.

有人可以提供ON DUPLICATE KEY UPDATE语句来防止ID值在更新时增加吗?

Can somebody provide an ON DUPLICATE KEY UPDATE statement that would prevent the ID value from increasing on update?

INSERT INTO table (Fruit, Color) VALUES(
    Apple, Red)
ON DUPLICATE KEY UPDATE (
{statement here})

推荐答案

必须将主键设置为ID列，然后将(水果，颜色)设置为唯一键.然后，您可以运行以下@Andrew B Anthony提供的查询:

Must make the primary key the ID column then set (Fruit, Color) as a unique key. Then you can run the query provided by @Andrew B Anthony below:

INSERT INTO table (Fruit, Color) VALUES
    Apple, Red)
ON DUPLICATE KEY UPDATE 
Fruit=values(Fruit),
Color=values (Color)

这篇关于如何在重复密钥更新时禁止自动递增ID更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！