重置自增有什么坏处吗？
我有 1 亿行，而且它变得太大了。 我看到很多差距。 （因为我删除、添加、删除、添加。）
我想用自动增量来填补这些空白。 如果我重置它......有什么危害吗？
如果我这样做，会填补空白吗？：
mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;
I have a 100 million rows, and it's getting too big.
I see a lot of gaps. (since I delete, add, delete, add.)
I want to fill these gaps with auto-increment.
If I do reset it..is there any harM?
If I do this, will it fill the gaps?:
mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
可能非常危险，因为您可以再次获得已在使用的号码。
您的建议是将序列再次重置为 1。它只会产生 1,2,3,4,5,6,7,.. 等等，无论这些数字是否存在间隙。
更新：根据Martin的回答，由于涉及危险，MySQL甚至不会让你这样做。它将把计数器重置为至少当前值+1。
再想想间隙的存在会导致什么真正的问题。通常这只是一个审美问题。
如果数字太大，请切换到更大的数据类型（bigint 应该足够了）。
Potentially very dangerous, because you can get a number again that is already in use.
What you propose is resetting the sequence to 1 again. It will just produce 1,2,3,4,5,6,7,.. and so on, regardless of these numbers being in a gap or not.
Update: According to Martin's answer, because of the dangers involved, MySQL will not even let you do that. It will reset the counter to at least the current value + 1.
Think again what real problem the existence of gaps causes. Usually it is only an aesthetic issue.
If the number gets too big, switch to a larger data type (bigint should be plenty).
FWIW ...根据 MySQL 文档 应用
其中
tbl包含现有数据应该没有效果：我运行了一个小测试，证实了 MyISAM 表的这一点。
所以你的问题的答案是：没有害处，也不会填补空白。正如其他响应者所说：更改数据类型看起来是最不痛苦的选择。
FWIW... According to the MySQL docs applying
where
tblcontains existing data should have no effect:I ran a small test that confirmed this for a MyISAM table.
So the answers to you questions are: no harm, and no it won't fill the gaps. As other responders have said: a change of data type looks like the least painful choice.
您很可能不会从这样做中获得任何好处，并且您可能会通过覆盖行轻松搞砸您的应用程序，因为您将重置 ID 的计数。 （换句话说，下次插入行时，它将覆盖 ID 为
1的行，然后覆盖2等）。差距？如果数字太大，只需将其更改为 更大的数字（例如BIGINT）。编辑：我纠正了。它根本不会做任何事情，这支持了我的观点，即您应该将列的类型更改为更大的整数类型。
BIGINT的最大可能值为 2^64，超过 18 quintillion。如果您目前只有 1 亿行，那么在可预见的未来，这应该足够了。Chances are you wouldn't gain anything from doing this, and you could easily screw up your application by overwriting rows, since you're going to reset the count for the IDs. (In other words, the next time you insert a row, it'll overwrite the row with ID
1, and then2, etc.) What will you gain from filling the gaps? If the number gets too big, just change it to a larger number (such asBIGINT).Edit: I stand corrected. It won't do anything at all, which supports my point that you should just change the type of the column to a larger integer type. The maximum possible value for a
BIGINTis 2^64, which is over 18 quintillion. If you only have 100 million rows at the moment, that should be plenty for the foreseeable future.我同意 musicfreak...整数 (
int(10)) 的最大值是 4,294,967,295（无符号粗略）。如果您需要更高，请切换到BIGINT，最高可达 18,446,744,073,709,551,615。I agree with musicfreak... The maximum for an integer (
int(10)) is 4,294,967,295 (unsigned ofcoarse). If you need to go even higher, switching toBIGINTbrings you up to 18,446,744,073,709,551,615.由于您无法更改下一个自动增量值，因此您还有其他选择。数据类型切换是可以完成的，但这对我来说似乎有点令人不安，因为实际上并没有那么多行。您必须确保您的代码可以处理那么大的 ID，这对您来说可能会困难，也可能不会困难。
你能做很多停机时间吗？如果是的话，我可以想到两个选项：
转储/重新加载数据。您可以这样做，这样它就不会保留 ID 号。例如，您可以使用 SELECT ... INTO 将无 ID 的数据复制到具有相同 DDL 的新表。然后删除旧表并将新表重命名为旧名称。根据数据量，这可能会花费大量时间（和临时磁盘空间）。
您可以编写一个小程序来发出 UPDATE 语句来更改 ID。如果您让它运行缓慢，随着时间的推移，它会“整理”您的 ID。然后您可以暂时停止插入（只需一两分钟），更新最后的 ID，然后重新启动。更新最后一个 ID 后，您可以将 AUTO_INCREMENT 值更改为下一个数字，您的洞就会消失。这不会导致任何真正的停机（至少在 InnoDB 上），但可能需要相当长的时间，具体取决于您的程序的攻击性。
当然，这两者都忽略了引用完整性。我假设这不是问题（日志语句不用作外键或类似的语句）。
Since you can't change the next auto-increment value, you have other options. The datatype switch could be done, but it seems a little unsettling to me since you don't actually have that many rows. You'd have to make sure your code can handle IDs that large, which may or may not be tough for you.
Are you able to do much downtime? If you are, there are two options I can think of:
Dump/reload the data. You can do this so it won't keep the ID numbers. For example you could use a SELECT ... INTO to copy the data, sans-IDs, to a new table with identical DDL. Then you drop the old table and rename the new table to the old name. Depending on how much data there is, this could take a noticeable about of time (and temporary disk space).
You could make a little program to issue UPDATE statements to change the IDs. If you let that run slowly, it would "defragment" your IDs over time. Then you could temporarily stop the inserts (just a minute or two), update the last IDs, then restart it. After updating the last IDs you can change the AUTO_INCREMENT value to be the next number and your hole will be gone. This shouldn't cause any real downtime (at least on InnoDB), but it could take quite a while depending on how aggressive your program is.
Of course, both of these ignore referential integrity. I'm assuming that's not a problem (log statements that aren't used as foreign keys, or some such).
是否存在差距真的很重要吗？
如果你真的想返回并填充它们，你可以随时关闭自动增量，并在每次你想插入一行时手动扫描下一个可用的 id ——当然，记住锁定表以避免竞争条件。但这需要做很多工作，却没有多少收获。
您真的需要代理键吗？根据数据（您没有提到模式），您可能可以找到一个自然键。
Does it really matter if there are gaps?
If you really want to go back and fill them, you can always turn off auto increment, and manually scan for the next available id every time you want to insert a row -- remembering to lock the table to avoid race conditions, of course. But it's a lot of work to do for not much gain.
Do you really need a surrogate key anyway? Depending on the data (you haven't mentioned a schema) you can probably find a natural key.