i'm new to the whole database scenario and just learning. I have a database mysql and i am using phpmyadmin to edit fields etc. I created a auto_incremenet 'id' field that is set as a primary. If i add 5 fields, the id will be '1,2,3,4,5'. If i delete all those fields and add another 5 fields, why does the ID go to '6,7,8,9,10'? (instead of going back to 0, since technically the other fields do not exist anymore so its unique right?). Is there a way to make the id be sequentially numeric? 1,2,3,4,5 ?
我是整个数据库场景的新手,只是学习。我有一个数据库mysql,我使用phpmyadmin编辑字段等。我创建了一个auto_incremenet'id'字段,设置为主要。如果我添加5个字段,则id将为'1,2,3,4,5'。如果我删除所有这些字段并添加另外5个字段,为什么ID会转到'6,7,8,9,10'? (而不是回到0,因为从技术上讲,其他领域不再存在,所以它的独特权利?)。有没有办法让id按顺序数字? 1,2,3,4,5?
5 个解决方案
#1
5
You don't need to worry about it. Your primary key should be an unsigned int which can hold large enough numbers.
你不必担心它。您的主键应该是unsigned int,它可以容纳足够多的数字。
#2
1
I believe this happens because when using a relational database system, you can "link" rows to other rows, usually by their id. If you start reusing IDs, then you might end up in confusing situations.
我相信这是因为在使用关系数据库系统时,您可以将行“链接”到其他行,通常是通过其ID。如果您开始重用ID,那么您可能会陷入困惑的境地。
You can reset the auto_increment counter to whatever you wish using the following query:
您可以使用以下查询将auto_increment计数器重置为您想要的任何内容:
ALTER TABLE tableName AUTO_INCREMENT=123
If you are deleting all rows in your table, you can use TRUNCATE which will also reset the counter.
如果要删除表中的所有行,可以使用TRUNCATE,它也会重置计数器。
TRUNCATE TABLE tableName
#3
1
I think the main reason for this behaviour is efficiency, it means that MySQL doesn't need to track which numbers are not used, it only need to know which number was last used.
我认为这种行为的主要原因是效率,这意味着MySQL不需要跟踪哪些数字没有被使用,它只需要知道上次使用的是哪个数字。
#4
0
Have a look at this question. But basically:
看看这个问题。但基本上:
ALTER TABLE mytable AUTO_INCREMENT = 1;
UPDATE
As mentioned, fiddling with auto generated PKs will indefinitely cause you a lot of headache. Or writing a bunch of boilerplate code to do housekeeping.
如上所述,摆弄自动生成的PK会无限期地让你头疼。或者写一堆样板代码来做家务。
#5
0
You will have to reset the auto increment, so you can renumber the auto_incremented values, read more about the solutions here http://www.dbuggr.com/milly/reset-auto-increment-mysql/.
您将不得不重置自动增量,因此您可以重新编号auto_incremented值,请在此处阅读有关解决方案的更多信息http://www.dbuggr.com/milly/reset-auto-increment-mysql/。
#1
5
You don't need to worry about it. Your primary key should be an unsigned int which can hold large enough numbers.
你不必担心它。您的主键应该是unsigned int,它可以容纳足够多的数字。
#2
1
I believe this happens because when using a relational database system, you can "link" rows to other rows, usually by their id. If you start reusing IDs, then you might end up in confusing situations.
我相信这是因为在使用关系数据库系统时,您可以将行“链接”到其他行,通常是通过其ID。如果您开始重用ID,那么您可能会陷入困惑的境地。
You can reset the auto_increment counter to whatever you wish using the following query:
您可以使用以下查询将auto_increment计数器重置为您想要的任何内容:
ALTER TABLE tableName AUTO_INCREMENT=123
If you are deleting all rows in your table, you can use TRUNCATE which will also reset the counter.
如果要删除表中的所有行,可以使用TRUNCATE,它也会重置计数器。
TRUNCATE TABLE tableName
#3
1
I think the main reason for this behaviour is efficiency, it means that MySQL doesn't need to track which numbers are not used, it only need to know which number was last used.
我认为这种行为的主要原因是效率,这意味着MySQL不需要跟踪哪些数字没有被使用,它只需要知道上次使用的是哪个数字。
#4
0
Have a look at this question. But basically:
看看这个问题。但基本上:
ALTER TABLE mytable AUTO_INCREMENT = 1;
UPDATE
As mentioned, fiddling with auto generated PKs will indefinitely cause you a lot of headache. Or writing a bunch of boilerplate code to do housekeeping.
如上所述,摆弄自动生成的PK会无限期地让你头疼。或者写一堆样板代码来做家务。
#5
0
You will have to reset the auto increment, so you can renumber the auto_incremented values, read more about the solutions here http://www.dbuggr.com/milly/reset-auto-increment-mysql/.
您将不得不重置自动增量,因此您可以重新编号auto_incremented值,请在此处阅读有关解决方案的更多信息http://www.dbuggr.com/milly/reset-auto-increment-mysql/。