blocks|key|743250|text|这可能非常危险，因为您可以再次获取已在使用的数字。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|743251|您建议的是再次将序列重置为1。它只会产生1,2,3,4,5,6,7，..以此类推，不管这些数字是否在空白处。|743252|更新:根据马丁的回答，由于涉及的危险，MySQL甚至不会让你这么做。它会将计数器至少重置为当前值%2B+1。|743253|再想一想，差距的存在会导致什么真正的问题。通常这只是一个美学问题。|743254|如果数字变得太大，请切换到更大的数据类型(bigint应该足够了)。|743255|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

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).

blocks|key|953688|text|这样做可能不会带来任何好处，而且很容易通过覆盖行来搞砸您的应用程序，因为您将重置ID的计数。(换句话说，下次插入一行时，它将用ID+1覆盖该行，然后用2覆盖该行，依此类推。)您将从填补这些空白中获得什么？如果数字太大，只需将其更改为larger+number+(如BIGINT)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|953689|953690|编辑：我站起来了。它根本不会做任何事情，这支持我的观点，即您应该将列的类型更改为更大的整数类型。BIGINT的最大可能值为2%5E64，这是超过18+quintillion的。如果您目前只有1亿行，那么在可预见的未来，这应该是足够的。|BOLD|953691|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html^0|1U|1|23|1|3O|6|38|D|0|0|0|0|3|21|B|2D|U|1C|6|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|-4|5|6|7|11|8|@]|D|@]|E|$]]|$1|G|3|H|5|6|7|12|8|@$9|13|A|14|B|I]|$9|15|A|16|B|I]|$9|17|A|18|B|I]|$9|19|A|1A|B|C]]|D|@]|E|$]]|$1|J|3|-4|5|6|7|1B|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]]]

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 <code>1</code>, and then <code>2</code>, etc.) What will you gain from filling the gaps? If the number gets too big, just change it to a <a href="http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html" rel="nofollow noreferrer">larger number</a> (such as <code>BIGINT</code>).

<hr>

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 <code>BIGINT</code> is 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.

blocks|key|2043702|text|我同意音乐怪胎..。整数(int(10))的最大值是4,294,967,295+(无符号粗略)。如果您需要更高的版本，切换到BIGINT将提供18,446,744,073,709,551,615。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2043703|entityMap^0|D|7|1Q|6|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

I agree with musicfreak... The maximum for an integer (<code>int(10)</code>) is 4,294,967,295 (unsigned ofcoarse). If you need to go even higher, switching to <code>BIGINT</code> brings you up to 18,446,744,073,709,551,615.

blocks|key|2362562|text|FWIW根据MySQL+docs申请|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2362563|ALTER+TABLE+tbl+AUTO_INCREMENT+=+1|code-block|syntax|javascript|2362564|tbl包含现有数据的位置应该不会有任何影响：|style|CODE|2362565|2362566|要更改用于新行的AUTO_INCREMENT计数器的值，请执行以下操作：|blockquote|2362567|2362568|ALTER+TABLE+t2+AUTO_INCREMENT+=+value；|2362569|不能将计数器重置为小于或等于已使用的任何值。对于MyISAM，如果该值小于或等于AUTO_INCREMENT列中当前的最大值，则将该值重置为当前最大值加1。对于InnoDB，如果该值小于列中的当前最大值，则不会发生错误，并且不会更改当前序列值。|2362570|2362571|我为一个MyISAM表运行了一个小测试，确认了这一点。|2362572|因此，对你的问题的答案是:没有坏处，也不会填补空白。正如其他应答者所说:数据类型的改变看起来是最不痛苦的选择。|2362573|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/alter-table.html^0|6|A|0|0|0|0|3|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|D|3|E|5|F|7|1C|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|1D|8|@$A|1E|B|1F|K|L]]|9|@]|C|$]]|$1|M|3|-4|5|6|7|1G|8|@]|9|@]|C|$]]|$1|N|3|O|5|P|7|1H|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|1I|8|@]|9|@]|C|$]]|$1|R|3|S|5|6|7|1J|8|@]|9|@]|C|$]]|$1|T|3|U|5|6|7|1K|8|@]|9|@]|C|$]]|$1|V|3|-4|5|6|7|1L|8|@]|9|@]|C|$]]|$1|W|3|X|5|6|7|1M|8|@]|9|@]|C|$]]|$1|Y|3|Z|5|6|7|1N|8|@]|9|@]|C|$]]|$1|10|3|-4|5|6|7|1O|8|@]|9|@]|C|$]]]|11|$12|$5|13|14|15|C|$16|17]]]]

FWIW... According to the <a href="http://dev.mysql.com/doc/refman/5.1/en/alter-table.html" rel="nofollow noreferrer">MySQL docs</a> applying

<pre><code>ALTER TABLE tbl AUTO_INCREMENT = 1
</code></pre>

where <code>tbl</code> contains existing data should have no effect:

<blockquote>
 To change the value of the
 AUTO_INCREMENT counter to be used for
 new rows, do this:
 
 ALTER TABLE t2 AUTO_INCREMENT = value;
 
 You cannot reset the counter to a
 value less than or equal to any that
 have already been used. For MyISAM, if
 the value is less than or equal to the
 maximum value currently in the
 AUTO_INCREMENT column, the value is
 reset to the current maximum plus one.
 For InnoDB, if the value is less than
 the current maximum value in the
 column, no error occurs and the
 current sequence value is not changed.
</blockquote>

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.

blocks|key|742852|text|由于您不能更改下一个自动增量值，因此您有其他选择。数据类型切换是可以完成的，但对我来说这似乎有点令人不安，因为您实际上没有那么多行。您必须确保您的代码能够处理这么大的‘d，这对您来说可能很难，也可能不难。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|742853|您是否能够进行长时间的停机？如果你是，我可以想到两个选择：|742854|742855|转储/重新加载数据。您可以这样做，这样它就不会保留ID号。例如，您可以使用SELECT+...INTO将数据sans-ID复制到具有相同DDL的新表中。然后删除旧表并将新表重命名为旧名称。根据数据量的不同，这可能会占用大量的时间(和临时磁盘空间)。|ordered-list-item|742856|您可以编写一个小程序来发出UPDATE语句来更改ID。如果您让它运行缓慢，随着时间的推移，它将对您的ID进行“碎片整理”。然后，您可以暂时停止插入(只需一两分钟)，更新最后一个it，然后重新启动。在更新最后的ID之后，您可以将AUTO_INCREMENT值更改为下一个数字，这样您的空洞就会消失。这应该不会导致任何真正的停机时间(至少在InnoDB上)，但这可能需要相当长的时间，这取决于您的程序的攻击性。|742857|742858|当然，这两种方法都忽略了引用完整性。我假设这不是问题(不用作外键的日志语句，或类似的语句)。|742859|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|R|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|S|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

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:

<ol>
<li>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).</li>
<li>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.</li>
</ol>

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).

blocks|key|2362647|text|是否存在差距真的很重要吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2362648|如果您真的想要返回并填充它们，您总是可以关闭自动递增，并在每次想要插入一行时手动扫描下一个可用的id+--当然，请记住锁定表以避免竞争条件。但这有很多工作要做，但收获不大。|2362649|你真的需要一个代理键吗？根据数据(您还没有提到模式)，您可能会找到一个自然键。|2362650|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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?:

<pre><code>mysql&gt; ALTER TABLE tbl AUTO_INCREMENT = 1;
</code></pre>

Is there any harm in resetting the auto-increment?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一亿行，而且越来越大了。我看到了很多差距。(因为我删除、添加、删除、添加。)我想用自动增量来填补这些空白。如果我真的重置了it..is，有没有harM？如果我这样做，它会填补空白吗？：mysql> ALTER TABLE tbl AUTO_INCREMENT = 1;

问重置自动增量有什么坏处吗？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问重置自动增量有什么坏处吗？EN