消除自动增量的差距 [英] remove gaps in auto increment

问题描述

说我有一个带有自动递增id字段的MySQL表，然后插入3行.然后，我删除第二行.现在，表的ID为1,3.我可以让MySQL纠正它并将其设置为1,2，而无需编写程序来做到这一点吗?

Say I have a MySQL table with an auto incrementing id field, then I insert 3 rows. Then, I delete the second row. Now the id's of the table go 1,3. Can I get MySQL to correct that and make it 1,2 without having to write a program to do so?

推荐答案

一旦创建，MySQL将不允许您更改自动索引列的索引.我要做的是删除自动索引"列，然后添加一个具有相同名称的新索引，mysql将无间隙地索引新生成的列.仅在自动索引与其余数据无关，而仅用作更新和删除的参考的表上执行此操作.

MySQL won't let you change the indexing of an Auto-Index column once it's created. What I do is delete the Auto-Index column and then add a new one with the same name, mysql will index the newly generated column with no gaps. Only do this on tables where the Auto-Index is not relevant to the rest of the data but merely used as a reference for updates and deletes.

例如，我最近刚刚对包含谚语的表做了此操作，其中仅当我更新或删除谚语时才使用自动索引"列，但由于通过随机抽取的谚语，我需要自动索引是连续的在1和谚语计数之间的数字，如果序列中有间隔，可能会导致随机数指向不存在的索引.

For example I recently did just that for a table containing proverbs where the Auto-Index column was only used when I updated or deleted a proverb but I needed the Auto-Index to be sequential as the proverbs are pulled out via a random number between 1 and the count of the proverbs, having gaps in the sequence could have led to the random number pointing to a non-existant index.

HTH

这篇关于消除自动增量的差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！