自动增量在 MySQL 中不会重置

Auto-increment is not resetting in MySQL

提问by Lawrence Dol

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.

我正在尝试设置一个脚本来将一组特定的测试数据生成到我的数据库中，在开始时我想在不删除约束的情况下清除相关表（因为测试数据不是重建约束的合适位置）并为每个表重置 AUTO_INCREMENT，因为设置测试数据要简单得多，如果我可以对许多 ID 进行硬编码。

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

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.

并且当记录被删除时，自动增量值不会恢复为 1，即使我能找到的所有文档和 SO 答案都表明这应该有效。

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

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

This is on an INNODB database.

这是在 INNODB 数据库上。

What am I missing?

我错过了什么？

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

（注意：由于存在约束，我不能使用 TRUNCATE）。

回答by Mark Ormston

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

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

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.

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

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

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

1. Explicitly insert your identities for your test data. MySQL doesn't have problems with this, unlike some other database engines
2. Delete and recreate your identity column (or just change it from being an identity), if the constraints aren't on it itself.
3. Not use an Identity column and use another method (such as a procedure or outside code) to control your Identity. This is really a last resort and I wouldn't generally recommend it...

1. 为测试数据显式插入您的身份。与其他一些数据库引擎不同，MySQL 没有这方面的问题
2. 如果约束不在其本身，则删除并重新创建您的身份列（或只是将其从身份更改为身份）。
3. 不使用 Identity 列并使用其他方法（例如过程或外部代码）来控制您的 Identity。这真的是最后的手段，我一般不会推荐它......

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

来自 OP 的说明：这是 (1) 这就是我所需要的。

回答by Namphibian

From what I can see about the alter table statement.

从我可以看到的关于alter table 语句的信息。

You can reset auto increment value by using the ALTER TABLE statement. The syntax of the ALTER TABLE statement to reset auto increment value is as follows:

您可以使用 ALTER TABLE 语句重置自动增量值。用于重置自动增量值的 ALTER TABLE 语句的语法如下：

ALTER TABLE table_name AUTO_INCREMENT = value;

You specify the table name after the ALTER TABLE clause and the value which we want to reset to in the expression AUTO_INCREMENT = value.

您在 ALTER TABLE 子句之后指定表名，并在表达式 AUTO_INCREMENT = value 中指定要重置的值。

Notice that the value must be greater than or equal to the current maximum value of the auto-increment column.

请注意，该值必须大于或等于自增列的当前最大值。

Which is where your problem lies I suspect. So basically you are left with a couple of options as follows:

我怀疑这就是你的问题所在。所以基本上你有几个选项如下：

1. TRUNCATE TABLE: which according to our discussion is not a option
2. DROP and RECREATE the table: A long and painful experience

3. ALTER auto number column: I have not tried this but you could theoretically alter the primary key column from auto number to a int and then make it a auto number again. Something like:

   ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT NOT NULL;
   ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT AUTONUMBER NOT NULL;
   

1. TRUNCATE TABLE：根据我们的讨论，这不是一个选项
2. 删除并重新创建表：漫长而痛苦的经历

3. ALTER 自动编号列：我还没有尝试过，但理论上您可以将主键列从自动编号更改为 int，然后再次将其设为自动编号。就像是：

   ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT NOT NULL;
   ALTER TABLE tblName MODIFY COLUMN pkKeyColumn  BIGINT AUTONUMBER NOT NULL;
   

Hope these help a little.

希望这些有点帮助。

回答by hd1

Can you not drop the relevant, auto increment columnand recreate it? Example follows:

您不能删除相关的自动增量列并重新创建它吗？示例如下：

;;assuming your column is called id and your table is tbl
ALTER TABLE tbl DROP COLUMN id;
ALTER TABLE tbl ADD COLUMN id BIGINT UNSIGNED DEFAULT 1 PRIMARY KEY FIRST;

This shouldwork, but I don't use MySQL, just going off the docs. If you need further help, leave a comment and I'll do my best to help out.

这应该有效，但我不使用 MySQL，只是离开文档。如果您需要进一步的帮助，请发表评论，我会尽力提供帮助。

回答by Luke359

I've run into this problem when I've deleted middle rows from my table. My answer would be to INSERT NEW DATA TO NOT EXISTING ID. I expect that my answer still be usefull even if it's PHP not MYSQL.

当我从表中删除中间行时，我遇到了这个问题。我的答案是将新数据插入到不存在的 ID 中。 我希望我的答案仍然有用，即使它是 PHP 而不是 MYSQL。

1. First fetch your data.
2. if found not existing row Insert values and exit;

3. else if not found in whole loop then do insertion for default value;

   $rez = mysql_query("SELECT * FROM users");
   $exists = 1;
   while($row = mysql_fetch_array($rez)){
           if ( $exists != $row{'id'} ){
                  echo "found not existing id: ".$exists;
                  INSERT INTO users VALUES($exists,.. ,..); 
                  exit;
            } $exists += 1;
    }
    INSERT INTO users VALUES(NULL,.. ,..);  ##auto_inc column converts NULL to latest
   

1. 首先获取您的数据。
2. 如果发现不存在的行插入值并退出；

3. 否则如果在整个循环中没有找到则插入默认值；

   $rez = mysql_query("SELECT * FROM users");
   $exists = 1;
   while($row = mysql_fetch_array($rez)){
           if ( $exists != $row{'id'} ){
                  echo "found not existing id: ".$exists;
                  INSERT INTO users VALUES($exists,.. ,..); 
                  exit;
            } $exists += 1;
    }
    INSERT INTO users VALUES(NULL,.. ,..);  ##auto_inc column converts NULL to latest
   

I HOPE it will help somehow.

我希望它会以某种方式有所帮助。

回答by Suspended

In non-problematic circumstances you can do

在没有问题的情况下，你可以做

ALTER TABLE tbl AUTO_INCREMENT = 0;

which brings auto_increment value to the lowest allowed at the time.

这使 auto_increment 值达到当时允许的最低值。

回答by King of approximate

ALTER TABLE tbl DROP COLUMN id;
ALTER TABLE tbl ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

在您的 phpMyAdmin 中

回答by dennis

ALTER TABLE table_name AUTO_INCREMENT = value;
This worked for me, I had to set it to the last record in my database while going through the operations panel never worked for me.

ALTER TABLE table_name AUTO_INCREMENT = value;
这对我有用，我不得不将它设置为我数据库中的最后一条记录，同时通过操作面板对我来说从来没有用过。

回答by caro

I'm sure this has been long answered but when i need to truncate and can't I just do a set foreign_key_checks = 0then run my truncate and then set foreign_key_checks = 1.

我确信这已经得到了很长时间的回答，但是当我需要截断而我不能只做一个set foreign_key_checks = 0然后运行我的 truncate 然后set foreign_key_checks = 1.