自动增量未在MySQL中重置 [英] Auto-increment is not resetting in MySQL

问题描述

我正在尝试建立一个脚本，以在数据库中生成一组特定的测试数据，在开始时，我想清除有关表而不删除约束(因为测试数据不适合放置在该位置)重建约束)并为每个表重置AUTO_INCREMENT，因为如果我可以对许多ID进行硬编码，则设置测试数据要简单得多.

I am trying to set up a script to generate a particular set of test data into my database, at the beginning of which I want to clear the tables concerned without dropping constraints (because the test data is not the appropriate place to be rebuilding constraints) and reset the AUTO_INCREMENT for each table since setting up the test data is much, much simpler if I can hard-code many of the IDs.

例如，我有两个这样的语句(几乎每个表都有一对):

For example, I have two statements like this (there's a pair for nearly every table):

DELETE FROM AppointmentAttr
ALTER TABLE AppointmentAttr AUTO_INCREMENT = 1

在删除记录的同时，即使我能找到的所有文档和SO回答都表明这应该可行，自动递增值也不会恢复为1.

and while the records are deleted, the auto-increment value is not reverting to 1, even though all the documentation and SO answers I can find indicate that this should work.

如果我在MySQL Workbench中执行相同的语句，它也不会还原它.

If I do the same statement in MySQL Workbench it also does not revert it.

这是在INNODB数据库上.

This is on an INNODB database.

我想念什么?

(注意:由于存在约束，我无法使用TRUNCATE).

(Note: I cannot use TRUNCATE due to the presence of constraints).

推荐答案

MySQL不允许您减小AUTO_INCREMENT值，如下所示: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

MySQL does not permit you to decrease the AUTO_INCREMENT value, as specified here: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

您不能将计数器重置为小于或等于当前使用的值.对于InnoDB和MyISAM，如果该值小于或等于AUTO_INCREMENT列中当前的最大值，则该值将重置为当前的最大AUTO_INCREMENT列值加1.

You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and 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 AUTO_INCREMENT column value plus one.

即使有您的限制，我也可以尝试以下方法之一:

Even with your constraints, I would try one of the following:

1. 为测试数据明确插入您的身份.与其他一些数据库引擎不同，MySQL对此没有任何问题
2. 如果约束本身不存在，请删除并重新创建您的标识列(或将其更改为标识).
3. 请勿使用身份"列，而应使用其他方法(例如过程或外部代码)来控制您的身份.这确实是不得已的办法，我一般不会推荐它...

OP的注释:这就是我需要的(1).

Note from OP: It was (1) that was what I needed.

这篇关于自动增量未在MySQL中重置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！