为什么在违反唯一键约束而不是主键约束时，MySQL中会出现自动增量间隔?
我知道在MySQL中使用INSERT ... ON DUPLICATE KEY UPDATE时，插入失败时会出现自动增量间隔.但是-我注意到，只有在违反唯一键约束的情况下才会出现这种差距.如果主键约束失败，则不会出现自动增量间隔.
I understand that when using INSERT ... ON DUPLICATE KEY UPDATE in MySQL there are auto increment gaps when an insert fails. However -- I've noticed that the gaps only occur when a unique key constraint is violated. If the primary key constraint fails, no auto increment gap occurs.
这两者之间有什么区别的原因是什么?
What's the reason for the difference between the two?
谢谢！
如果自动增量字段是主键，则只有当您在insert语句中将自动增量字段的值显式设置为时，主键中的重复项才会发生该字段中已经存在的值(您为什么要这样做超出我的范围).
If the auto increment field is the primary key, then duplication in the primary key can only happen if you explicitly set the auto increment field's value in the insert statement to an already existing value in that field (why you would do this is beyond me).
作为使用自动增量上的mysql文档说:
当您将其他任何值插入到AUTO_INCREMENT列中时，该列将被设置为该值并重置顺序，以便下一个自动生成的值从最大列的值开始依次出现.
When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value.
要点是，在这种情况下，mysql不会尝试为自动增量列生成值，而是将使用插入中指定的值.此外，该序列还将在该字段中重置为最大值，因此下一个插入片段将没有任何间隙.
The point is, that in this case mysql does not try to generate a value for the auto increment column, but will use the value specified in the insert. Moreover, the sequence is also reset to the max value in the field, therefore the next insert will not have any gaps.
但是，如果在尝试插入时另一个唯一索引约束失败，则mysql已经为自动增量字段生成了值，因此在自动增量序列中会创建一个间隔.
However, if another unique index constraint fails when you attempt the insert, then mysql has already generated the value for the auto increment field, therefore a gap is created in the auto increment sequence.