InnoDB中的auto_increment值? [英] Auto_increment values in InnoDB?
问题描述
我一直在将InnoDB用于一个项目，并且依赖于auto_increment.对于大多数表来说，这不是问题，但是对于具有删除功能的表，这可能是一个问题:
I've been using InnoDB for a project, and relying on auto_increment. This is not a problem for most of the tables, but for tables with deletion, this might be an issue:
特别是这部分:
名为ai_col的AUTO_INCREMENT列:服务器启动后，对于第一次插入表t，InnoDB执行以下语句的等效项: 从t中选择MAX(ai_col)进行更新; InnoDB将语句检索的值加1，然后将其分配给表的列和表的自动增量计数器.
AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement: SELECT MAX(ai_col) FROM t FOR UPDATE; InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table.
这是一个问题，因为尽管它确保表中的键是唯一的，但是此表中有外键，这些键不再是唯一的.
This is a problem because while it ensures that within the table, the key is unique, there are foreign keys to this table where those keys are no longer unique.
mysql服务器不会/不应该经常重启，但这已经中断了.有什么简单的方法可以解决这个问题吗?
The mysql server does/should not restart often, but this is breaking. Are there any easy ways around this?
推荐答案
如果您有外键约束，那么当表B引用该行时，如何从表A中删除该行?对我来说这似乎是一个错误.
If you have a foreign key constraint, how can you delete a row from table A when table B references that row? That seems like an error to me.
无论如何，通过在应用程序启动时重置偏移量，可以避免重复使用自动增量值.在所有引用表A的表中查询最大值，然后将表更改为大于该最大值的表，例如如果最大为989，请使用以下代码:
Regardless, you can avoid the reuse of auto-increment values by resetting the offset when your application starts back up. Query for the maximum in all the tables that reference table A, then alter the table above that maximum, e.g. if the max is 989, use this:
alter table TableA auto_increment=999;
还请注意，不同的MySQL引擎具有不同的自动递增行为.此技巧适用于InnoDB.
Also beware that different MySQL engines have different auto-increment behavior. This trick works for InnoDB.
这篇关于InnoDB中的auto_increment值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
