在MySQL中删除后自动递增 [英] Auto Increment after delete in MySQL
问题描述
我有一个带有主键字段且启用了AUTO_INCREMENT的MySQL表. 在阅读了这里的其他文章后，我发现人们遇到了同样的问题，答案也各不相同.有些人建议不要使用此功能，另一些人则说它不能被修复".
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，但是由于最后输入的courseID为18，所以它将为19.
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.
考虑到所有建议，我决定忽略此问题".我将简单地删除和添加记录，同时让自动增量完成它的工作.我猜数字到底有多大无关紧要，因为它仅用作唯一标识符，并且没有(如上所述) business 的含义.
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?
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屋！
