Sequential 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
Death
Forum Newbie
Posts: 17
Joined: Tue Apr 19, 2005 11:52 am

Sequential Auto-Increment ?

Post by Death »

Hi guys,

I want to have an ID column with sequential integers so I can choose random ID numbers and select those rows. The problem is when I delete a row; it leaves a hole.

How should I handle this ? It would be great if I could shift them all down, so if I have 0,1,2,3,4 and remove ID 2 it becomes 0,1,2,3 and *not* 0,1,3,4.

Any help is appreciated.

Robert
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Leave it as it is, and when you want a few random rows use:

"select * from table order by rand limit 5"

Then you'll have 5 random rows.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

I've had cases which required sequencial id's.. so I would run a query like:

Code: Select all

mysql_query("DELETE FROM `table` WHERE `id` = '".$_GET['id']."' LIMIT 1");
mysql_query("UPDATE `table` SET `id` = (`id`-1) WHERE `id` >= '".$_GET['id']."'";
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Btw, if you run that query... make sure they are in one (=1) transaction. Because at a given point things will go wrong if they aren't...
Post Reply