在重复键+自动增加问题mysql [英] ON DUPLICATE KEY + AUTO INCREMENT issue mysql

问题描述

我有这样的表结构

当我在表中插入行时，我正在使用以下查询:

when I insert row to the table I'm using this query:

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

当我第一次插入数据而不触发ON DUPLICATE KEY时，id会逐渐增加:

when I first insert data without triggering the ON DUPLICATE KEY the id increments fine:

但是当ON DUPLICATE KEY触发并且我插入新行时，id对我来说很奇怪:

but when the ON DUPLICATE KEY triggers and i INSERT A NEW ROW the id looks odd to me:

我如何保持auto increment，即使它触发了ON DUPLICATE KEY，也能正确递增?

How can I keep the auto increment, increment properly even when it triggers ON DUPLICATE KEY?

推荐答案

此行为已记录(括号中的段落):

This behavior is documented (paragraph in parentheses):

如果您指定ON DUPLICATE KEY UPDATE，并插入一行 会在UNIQUE索引或PRIMARY KEY中导致重复值，MySQL 对旧行执行UPDATE.例如，如果列a为 声明为UNIQUE并包含值1，以下两个 语句具有类似的效果:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;

(效果不相同 一个InnoDB表，其中a是一个自动增量列.与 自动增加列，则INSERT语句会增加 自动增加值，但UPDATE没有.)

(The effects are not identical for an InnoDB table where a is an auto-increment column. With an auto-increment column, an INSERT statement increases the auto-increment value but UPDATE does not.)

这里是一个简单的解释. MySQL尝试首先执行插入操作.这是ID自动递增的时间.一旦增加，它将保持不变.然后检测到重复项并进行更新.但是价值被错过了.

Here is a simple explanation. MySQL attempts to do the insert first. This is when the id gets auto incremented. Once increment, it stays. Then the duplicate is detected and the update happens. But the value gets missed.

您不应依赖auto_increment之间没有缝隙.如果这是必需的，则更新和插入的开销会更大.本质上，您需要在整个表上加一个锁，并对需要重新编号的所有内容进行重新编号，通常使用触发器.更好的解决方案是计算输出的增量值.

You should not depend on auto_increment having no gaps. If that is a requirement, the overhead on the updates and inserts is much larger. Essentially, you need to put a lock on the entire table, and renumber everything that needs to be renumbered, typically using a trigger. A better solution is to calculate incremental values on output.

这篇关于在重复键+自动增加问题mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！