即使由于错误而插入失败，MySQL自动增量值也会增加 [英] MySQL autoincrement value increases even when insertion fails due to error

问题描述

当我从用户那里接收数据时，我每次都会验证输入的格式(PHP).我唯一无法验证(或我不想)的是约束(例如外键)违规.我让DML引发并出错，并让数据库驱动程序引发异常.捕获它后，我只是通过MySQL的错误代码打印出一些错误消息.

As I receive data from the user, I validate the format of the input every time (PHP). The only thing I can't validate (or I wouldn't want to) is the constraint (for example foreign key) violation. I let the DML to raise and error and the database driver to throw an exception. After catching it I just print out some error message by MySQL's error code.

问题是，即使插入失败，我仍然看到autoincrement序列仍在增长.我该如何预防?

The problem is, even when insertion fails, I see that the autoincrement sequence still grows, increases. How can I prevent that?

推荐答案

这是自动增量值和序列的工作方式.使用值时，如果事务失败，则不会收回该值，并且会回滚该事务.

That is how auto increment values and sequences work. When a value is used, it is not reclaimed if the transaction fails, and the transaction is rolled back.

由于您使用的是自动增量值，因此实际值并不重要，因此除了缺少数字的美观性之外，空格也不应该成为问题.

Since you are using auto increment values, the actual value does not matter so the spaces should not be a problem, apart from the aesthetics of having missing numbers.

这篇关于即使由于错误而插入失败，MySQL自动增量值也会增加的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！