Page 1 of 1

Sequential Auto-Increment ?

Posted: Sat Jun 18, 2005 5:12 pm
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

Posted: Sat Jun 18, 2005 5:20 pm
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.

Posted: Sat Jun 18, 2005 5:26 pm
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']."'";

Posted: Sat Jun 18, 2005 6:00 pm
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...