ON DUPLICATE KEY + AUTO INCREMENT 问题 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 触发并且我 INSERT A NEW ROW 时，id 对我来说看起来很奇怪:

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

如何保持自动递增，即使触发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执行旧行的更新.例如，如果列 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 incremented, 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.

这篇关于ON DUPLICATE KEY + AUTO INCREMENT 问题 mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！