Fix auto increment mysql

Question

Possible Duplicate:
MySQL: Reorder/Reset auto increment primary key?

I have an auto increment field in my sql, by accident i deleted something now my tables are off because the auto increment number jumped. I forgot the exact sql command but i remember there was an Select table set auto increment='whatever' i just forgot the syntax, can anyone help?

I am getting the vibe from your question that it is significant the auto increment is in order. If so, why? — Malkus, Nov 12 '12 at 20:39
because when i made my database i was stupid enough to do it that way =( in the mean time i need to fix the issue before i redo my schema — Dnaso, Nov 12 '12 at 20:45
It happens to the best of us, we are all learning. I would recommend doing a dump of the database and testing any commands against a local instance to avoid further corruption. — Malkus, Nov 12 '12 at 20:48
yeah i mean i was NEW NEW to programming and my db is now huge so like to do it over again is BLAH one of those things, ill get around to it though eventually, and i think i should maybe start on a fresh server so i dont patch up crap — Dnaso, Nov 12 '12 at 20:51

score 7 · Accepted Answer · answered Nov 12 '12 at 20:38

7

ALTER TABLE theTableInQuestion AUTO_INCREMENT=1234

answered Nov 12 '12 at 20:38

Malkus

3,686
2
24
39

score 4 · Answer 2 · edited Nov 12 '12 at 20:38

4

ALTER TABLE YOUR_TABLE AUTO_INCREMENT=YOUR_DESIRED_NUMBER;

edited Nov 12 '12 at 20:38

Marc

16,170
20
76
119

answered Nov 12 '12 at 20:38

m4t1t0

5,669
3
22
30

score 1 · Answer 3 · edited Nov 12 '12 at 20:42

1

See Below:

ALTER TABLE tbl AUTO_INCREMENT = newNumber;

edited Nov 12 '12 at 20:42

Malkus

3,686
2
24
39

answered Nov 12 '12 at 20:38

Matt Clark

27,671
19
68
123

score 1 · Answer 4 · answered Nov 12 '12 at 20:45

1

According to the manual:

ALTER table drop column autoinc_column;
ALTER table add column autoinc_column ...;

Where "..." is the same parameters used to create the initial column.

answered Nov 12 '12 at 20:45

hd1

33,938
5
80
91

More work then is needed, you can just reassign a value to AUTO_INCREMENT. – Matt Clark Nov 12 '12 at 20:50
This is what must be done if you are trying to redo the auto increment instead of just resetting the auto increment number – PBwebD Feb 11 '15 at 21:04

Fix auto increment mysql

4 Answers4