Mysql resetting id field

Question

I'm wondering is it possible to reset id field, let's say now i have 5000 records and id is auto incrementing and after some time i deleted some records so now it's going like 1, 5, 10, 11, 23 and so on, it's not realy a issue for me, i just wondering is it possible to make all id's go in order 1,2,3,4,5 and so on

It is possible but you don't want to do that, ever. Forget about "resetting" the id counter. — N.B., Jan 30 '12 at 13:56

score 3 · Answer 1 · answered Jan 30 '12 at 13:36

3

I would first ask why you want them to go in order. If its just OCD then forget about it. If its for a programmatic reason, you should never be reliant on surrogate keys. I would recommend you rework your logic so you have some type of natural key, and code around using that.

answered Jan 30 '12 at 13:36

Nix

57,072
29
149
198

that's actualy isn't related to anything i was just playing around with my database and thought of something like that :D – Linas Jan 30 '12 at 13:37

score 1 · Accepted Answer · answered Jan 30 '12 at 14:08

Why did you need this? If your ID field unsigned bigint type, than you can have 9223372036854775808 values. Is that not enough for you? But, if you still think this a problem, you should look to stored procedure feature: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html. In the procedure you must compare current record with previous and next one's and shift the ID's if there is a "free" number. In the end of procedure you just can set increment value such like this: ALTER TABLE table_name AUTO_INCREMENT = last_record+1. There's no way to do this with MySQL build-in features.

Mysql resetting id field

2 Answers2