MySQL删除后自动递增 [英] Auto Increment after delete in MySQL

问题描述

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

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'.

我有:

table: course
fields: courseID, courseName

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

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.

我的 SQL 知识并不惊人，但是否可以通过查询(或 phpMyAdmin 界面中的设置)刷新或更新此计数?

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)?

此表将与数据库中的其他表相关联.

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) business meaning.

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

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.

推荐答案

您尝试做的事情听起来很危险，因为那不是 AUTO_INCREMENT 的预期用途.

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_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

退一步问为什么需要回收键值?" 未签名的INT(或BIGINT)是否不提供足够大的密钥空间?

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?

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

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

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