11

I was looking at my table in phpmyadmin and noticed that the id starts at 13410 and increase by 1. I would like to reset and start from one. I've read many people say its better to leave it alone or its going to get complicated if you messed with it but I still need a solution to at least start the id at 1. Thanks

merrill
  • 593
  • 5
  • 14
  • 34

3 Answers3

21
ALTER TABLE table AUTO_INCREMENT = 1

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

I hope you know what you do!

MatTheCat
  • 18,071
  • 6
  • 54
  • 69
  • you and AKhil gave a very simple answer which I love but im wondering is it going to affect the data or make any complicating doing this? For some reason I feel safer doing the code that Kae provided. What are your suggestion or opinion about that? – merrill Nov 15 '11 at 09:29
10
update *tablename* set id=id-13409;

then find the highest id: select id from tablename order by id desc limit 1;

then reset the auto_increment: alter table tablename auto_increment=12345; (change 12345 to the highest ID plus one)

Kae Verens
  • 4,076
  • 3
  • 21
  • 41
  • can you make it more specific? table name is 'author' column name is 'id' lowest id is 13410 last id is 26800 and of course auto-increment after that. thanks! – merrill Nov 15 '11 at 09:26
  • I did the first line and it went to 1(it works) and wonder if I still need to do the following two lines you mentioned? The type is ID(11) and extra is auto_increment at the moment and wasn't sure if I still still do the following two code line. – merrill Nov 16 '11 at 04:10
1
You can reset the auto increment to desired value using the following statement , 
eg : mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;
Akhil Thayyil
  • 9,263
  • 6
  • 34
  • 48