自动递增跳过数字？ [英] Auto Increment skipping numbers?

问题描述

注意：我刚刚接触数据库和PHP

Note: I'm new to databases and PHP

我有一个订单 自动增量和独特。

http://f.cl.ly/items/3u2D1l1j2l0j0A16450I/Image% 202013-07-22％20at％207.00.15％20 PM.png

在我的PHP脚本中，我使用AJAX获取新数据，但问题也就是说， order 跳过数字并且明显更高，因此迫使我在插入数据时手动更新数字。在这种情况下，我最终会将 782 更改为 38 。

In my PHP script, I am using AJAX to get new data but the problem with that is, is that the order skips numbers and is substantially higher thus forcing me to manually update the numbers when the data is inserted. In this case I would end up changing 782 to 38.

http://f.cl.ly /items/0N2J1w1Y310E2H2o0m05/Image%202013-07-22%20at%206.49.20%20PM.png

$SQL = "INSERT IGNORE INTO `read`(`title`,`url`) VALUES\n ".implode( "\n,",array_reverse( $sql_values ) );

如何让它增加+1？

推荐答案

如果INSERT失败，MySQL 5.1及更高版本中的默认auto_increment行为将丢失自动递增值。也就是说，它每次增加1，但是如果INSERT失败，则不撤销增量。这是不常见的，失去〜750值，但不是不可能的（我查询了一个网站，每个成功的INSERT跳过1500）。

The default auto_increment behavior in MySQL 5.1 and later will "lose" auto-increment values if the INSERT fails. That is, it increments by 1 each time, but doesn't undo an increment if the INSERT fails. It's uncommon to lose ~750 values but not impossible (I consulted for a site that was skipping 1500 for every INSERT that succeeded).

您可以更改 innodb_autoinc_lock_mode = 0 以使用MySQL 5.0行为，并避免在某些情况下丢失值。请参见 http://dev.mysql.com/ doc / refman / 5.1 / en / innodb-auto-increment-handling.html 了解详情。

You can change innodb_autoinc_lock_mode=0 to use MySQL 5.0 behavior and avoid losing values in some cases. See http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html for more details.

另一件要检查的事情是 auto_increment_increment 配置变量。默认情况下它是1，但你可能已经改变了这一点。

Another thing to check is the value of the auto_increment_increment config variable. It's 1 by default, but you may have changed this. Again, very uncommon to set it to something higher than 1 or 2, but possible.

我同意其他注释者，autoinc列的意图是唯一的，但不一定是连续。你可能不应该担心这么多，除非你提高的autoinc价值这么快，以至于你可以运行一个INT的范围（这发生在我身上）。

I agree with other commenters, autoinc columns are intended to be unique, but not necessarily consecutive. You probably shouldn't worry about it so much unless you're advancing the autoinc value so rapidly that you could run out of the range of an INT (this has happened to me).

你如何解决它跳过1500为什么插入？

How exactly did you fix it skipping 1500 for ever insert?

INSERT失败的原因是存在另一个具有UNIQUE约束的列，并且INSERT尝试在该列中插入重复值。阅读我链接到的手册页，了解为什么这是重要的细节。

The cause of the INSERT failing was that there was another column with a UNIQUE constraint on it, and the INSERT was trying to insert duplicate values in that column. Read the manual page I linked to for details on why this matters.

修复是先做一个SELECT检查值的存在，然后尝试插入它。这违背了普通的智慧，这只是尝试INSERT并处理任何重复的键异常。但在这种情况下，失败的INSERT的副作用导致自动inc值丢失。执行SELECT首先消除了几乎所有这些异常。

The fix was to do a SELECT first to check for existence of the value before attempting to INSERT it. This goes against common wisdom, which is to just try the INSERT and handle any duplicate key exception. But in this case, the side-effect of the failed INSERT caused an auto-inc value to be lost. Doing a SELECT first eliminated almost all such exceptions.

但是你也必须处理一个可能的异常，即使你先SELECT。

But you also have to handle a possible exception, even if you SELECT first. You still have a race condition.

你说得对！ innodb_autoinc_lock_mode = 0工作像一个魅力。

You're right! innodb_autoinc_lock_mode=0 worked like a charm.

在这种情况下，我想知道为什么这么多插入失败。我怀疑，像许多SQL开发人员一样，在AJAX处理程序中执行INSERT之后，不会检查成功状态，因此您永远不会知道这么多失败。

In your case, I would want to know why so many inserts are failing. I suspect that like many SQL developers, you aren't checking for success status after you do your INSERTs in your AJAX handler, so you never know that so many of them are failing.

他们可能仍然失败，你只是没有失去auto-inc id的副作用。你应该真正地诊断为什么这么多失败发生。您可能会产生不完整的资料，或执行的交易次数多于必要。

They're probably still failing, you just aren't losing auto-inc id's as a side effect. You should really diagnose why so many fails occur. You could be either generating incomplete data, or running many more transactions than necessary.

这篇关于自动递增跳过数字？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！