Mysql delete (auto_increment)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

Mysql delete (auto_increment)

Post by Pizmal »

I have a question on how mysql works. If i have a PK that is auto_increment numbers....

example

1 .. 2 .. 3 .. 4 .. 5

If i delete 3. I understand 6 would be the next number. Why does it go

1 .. 2 .. 6 .. 4 .. 5


??

Thanks for you info

From
Pizmal
User avatar
tim
DevNet Resident
Posts: 1165
Joined: Thu Feb 12, 2004 7:19 pm
Location: ohio

Post by tim »

b/c thats the way it is, it cannot reset all the numbers b/c one row is deleted.

however, if there be a need to do something to that effect, mySQL has the function:

TRUNCATE
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

Post by Pizmal »

I dont want to reset the numbers just dont want that number in the wrong order. If that is how it is .... I guess everyone "coding people" use php or scripting to reorder them with some sort of sort() ?

From
Pizmal
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

6 will probably appear there because that's the most efficient place to stick the information.. remember mysql is trying to minimize storage space required.. if you used ORDER BY that id, then it'll always appear in order of inserts..
Pizmal
Forum Newbie
Posts: 19
Joined: Wed Jul 14, 2004 7:43 pm
Location: USA

Post by Pizmal »

That is what I wanted to know. I have problems determining if that is how it is suppose to act.

From
Pizmal
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

just use ORDER BY..
Post Reply