MySQL MySQL删除后自动递增

Auto Increment after delete in MySQL

提问by OmidTahouri

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

我有一个 MySQL 表，主键字段上有 AUTO_INCREMENT。在阅读了这里的其他帖子后，我注意到有人遇到了同样的问题，答案也各不相同。有些人建议不要使用此功能，其他人则表示无法“修复”。

I have:

我有：

table: course
fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

示例：表中的记录数：18。如果我删除记录 16、17 和 18 - 我希望输入的下一条记录的 courseID 为 16，但是它将是 19，因为最后输入的 courseID 是 18。

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

我的 SQL 知识并不惊人，但无论如何可以使用查询（或 phpMyAdmin 界面中的设置）刷新或更新此计数？

This table will relate to others in a database.

该表将与数据库中的其他表相关。

---

---

Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) businessmeaning.

鉴于所有的建议，我决定忽略这个“问题”。我将简单地删除和添加记录，同时让自动增量完成它的工作。我想数字是什么并不重要，因为它仅用作唯一标识符并且没有（如上所述）商业意义。

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

对于那些我可能对我的原始帖子感到困惑的人：我不想使用这个字段来知道我有多少记录。我只是想让数据库看起来整洁，并有更多的一致性。

回答by Dolph

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

您尝试做的事情听起来很危险，因为这不是AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENTat all, and manage your keys manually. However, this is NOT a recommended practice.

如果您真的想找到最低的未使用密钥值，请完全不要使用AUTO_INCREMENT，并手动管理您的密钥。但是，这不是推荐的做法。

Take a step back and ask "why you need to recycle key values?" Do unsigned INT(or BIGINT) not provide a large enough key space?

退后一步，问“为什么需要回收键值？” 无符号INT（或BIGINT）是否不能提供足够大的键空间？

Are you really going to have more than 18,446,744,073,709,551,615unique records over the course of your application's lifetime?

18,446,744,073,709,551,615在应用程序的整个生命周期中，您真的会拥有更多的唯一记录吗？

回答by monksp

ALTER TABLE foo AUTO_INCREMENT=1

If you've deleted the most recent entries, that should set it to use the next lowest available one. As in, as long as there's no 19 already, deleting 16-18 will reset the autoincrement to use 16.

如果您删除了最近的条目，则应将其设置为使用下一个可用的最低条目。就像，只要已经没有 19，删除 16-18 就会将自动增量重置为使用 16。

---

---

EDIT:I missed the bit about phpmyadmin. You can set it there, too. Go to the table screen, and click the operations tab. There's an AUTOINCREMENTfield there that you can set to whatever you need manually.

编辑：我错过了有关 phpmyadmin 的部分。你也可以在那里设置。转到表格屏幕，然后单击操作选项卡。那里有一个AUTOINCREMENT字段，您可以手动设置为您需要的任何内容。

回答by Darin Dimitrov

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any businessmeaning. So leave the primary key as is and add another column called for example courseOrder. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the courseOrdercolumn of all rows that have courseOrdergreater than the one you are currently deleting.

数据库中的主自增键用于唯一标识给定的行，不应赋予任何业务意义。因此，保留主键原样并添加另一个名为 example 的列courseOrder。然后，当您从数据库中删除一条记录时，您可能需要发送一个额外的 UPDATE 语句，以便减少courseOrder所有行的列，这些行的列courseOrder大于您当前正在删除的行。

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

作为旁注，您永远不应该修改关系数据库中主键的值，因为可能有其他表将其作为外键引用并且修改它可能会违反引用约束。

回答by Claod

Try :

尝试 ：

SET @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE tableNameAUTO_INCREMENT = 1;

设置@num := 0;

更新 your_table SET id = @num := (@num+1);

更改表tableNameAUTO_INCREMENT = 1;

That'll reset the autoincremented value, and then count every row while a new value is created for it.

这将重置自动递增的值，然后在为其创建新值时计算每一行。

example : before

例子：之前

• 1 : first value here
• 2 : second value here
• X : deleted value
• 4 : The rest of the table
• 5 : The rest of the rest..

• 1：这里的第一个值
• 2：这里的第二个值
• X : 删除值
• 4：桌子的其余部分
• 5 : 剩下的..

so the table will display the array : 1,2,4,5

所以表格将显示数组：1,2,4,5

Example : AFTER (if you use this command you will obtain)

示例：AFTER（如果您使用此命令，您将获得）

• 1 : first value here
• 2 : second value here
• 3 : The rest of the table
• 4 : the rest of the rest

• 1：这里的第一个值
• 2：这里的第二个值
• 3：桌子的其余部分
• 4 : 剩下的

No trace of the deleted value, and the rest of the incremented continues with this new count.

没有被删除的值的痕迹，其余的增量继续这个新的计数。

BUT

但

1. If somewhere on your code something use the autoincremented value... maybe this attribution will cause problem.
2. If you don't use this value in your code everything should be ok.

1. 如果您的代码中的某处使用了自动递增的值......也许这种归因会导致问题。
2. 如果您不在代码中使用此值，一切都应该没问题。

回答by Mike Sherov

You shouldn't be relying on the AUTO_INCREMENT id to tell you how many records you have in the table. You should be using SELECT COUNT(*) FROM course. ID's are there to uniquely identifiy the course and can be used as references in other tables, so you shouldn't repeat ids and shouldn't be seeking to reset the auto increment field.

您不应该依赖 AUTO_INCREMENT id 来告诉您表中有多少条记录。你应该使用SELECT COUNT(*) FROM course. ID 用于唯一标识课程并可用作其他表中的引用，因此您不应重复 ID，也不应寻求重置自动增量字段。

回答by Michel Ayres

I came here looking for an answer to the Title question "MySQL - Auto Increment after delete"but I could only find an answer for that in the questions

我来这里是为了寻找标题问题的答案，"MySQL - Auto Increment after delete"但我只能在问题中找到答案

• How to delete certain row from mysql table?
• How to reset AUTO_INCREMENT in MySQL?

• 如何从mysql表中删除某些行？
• 如何在 MySQL 中重置 AUTO_INCREMENT？

By using something like:

通过使用类似的东西：

DELETE FROM table;
ALTER TABLE table AUTO_INCREMENT = 1;

---

---

Note that Darin Dimitrov's answerexplain really well AUTO_INCREMENTand it's usage. Take a look there before doing something you might regret.

请注意，达林·迪米特洛夫 (Darin Dimitrov) 的回答很好地解释了AUTO_INCREMENT它的用法。在做你可能会后悔的事情之前先看看那里。

PS: The question itself is more "Why you need to recycle key values?"and Dolph's answercover that.

PS：问题本身更多"Why you need to recycle key values?"，Dolph 的回答涵盖了这一点。

回答by user262976

you can select the ids like so:

您可以像这样选择 ID：

set @rank = 0;
select id, @rank:=@rank+1 from tbl order by id

the result is a list of ids, and their positions in the sequence.

结果是一个 id 列表，以及它们在序列中的位置。

you can also reset the ids like so:

您还可以像这样重置 ID：

set @rank = 0;
update tbl a join (select id, @rank:=@rank+1 as rank from tbl order by id) b
  on a.id = b.id set a.id = b.rank;

you could also just print out the first unused id like so:

您也可以像这样打印出第一个未使用的 ID：

select min(id) as next_id from ((select a.id from (select 1 as id) a
  left join tbl b on a.id = b.id where b.id is null) union
  (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id
  where b.id is null)) c;

after each insert, you can reset the auto_increment:

每次插入后，您可以重置 auto_increment：

alter table tbl auto_increment = 16

or explicitly set the id value when doing the insert:

或在执行插入时显式设置 id 值：

insert into tbl values (16, 'something');

typically this isn't necessary, you have count(*)and the ability to create a ranking number in your result sets. a typical ranking might be:

通常这不是必需的，您可以count(*)在结果集中创建排名数字。一个典型的排名可能是：

set @rank = 0;
select a.name, a.amount, b.rank from cust a,
  (select amount, @rank:=@rank+1 as rank from cust order by amount desc) b
  where a.amount = b.amount

customers ranked by amount spent.

客户按消费金额排名。

回答by Peter Smartt

I can think of plenty of scenarios where you might need to do this, particularly during a migration or development process. For instance, I just now had to create a new table by cross-joining two existing tables (as part of a complex set-up process), and then I needed to add a primary key after the event. You can drop the existing primary key column, and then do this.

我可以想到很多您可能需要执行此操作的场景，尤其是在迁移或开发过程中。例如，我刚才必须通过交叉连接两个现有表来创建一个新表（作为复杂设置过程的一部分），然后我需要在事件之后添加一个主键。您可以删除现有的主键列，然后执行此操作。

ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);

For a live system, it is not a good idea, and especially if there are other tables with foreign keys pointing to it.

对于实时系统来说，这不是一个好主意，尤其是如果还有其他带有外键指向它的表。

回答by Geni Jaho

There is actually a way to fix that. First you delete the auto_incremented primary key column, and then you add it again, like this:

实际上有一种方法可以解决这个问题。首先删除 auto_incremented 主键列，然后再次添加它，如下所示：

ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name ADD column_name int not null auto_increment primary key first;

回答by Charles Robertson

What you are trying to do is very dangerous. Think about this carefully. There is a very good reason for the default behaviour of auto increment.

你试图做的事情是非常危险的。仔细想想。自动增量的默认行为有一个很好的理由。

Consider this:

考虑一下：

A record is deleted in one table that has a relationship with another table. The corresponding record in the second table cannot be deleted for auditing reasons. This record becomes orphaned from the first table. If a new record is inserted into the first table, and a sequential primary key is used, this record is now linked to the orphan. Obviously, this is bad. By using an auto incremented PK, an id that has never been used before is always guaranteed. This means that orphans remain orphans, which is correct.

删除一个表中与另一个表有关系的记录。由于审计原因，不能删除第二个表中的相应记录。该记录从第一个表中成为孤立的。如果将新记录插入到第一个表中，并使用顺序主键，则此记录现在链接到孤立记录。显然，这是不好的。通过使用自动递增的 PK，始终可以保证以前从未使用过的 id。这意味着孤儿仍然是孤儿，这是正确的。