Sequence in MySQL

[fierdor]

I need to have a sequential field in one of my SQL tables such that even if i add or delete a row the other numbers should automatically adjust and make a sequence..

Autoincrement is one option but it wont work if I delete a certain row in the table...

Is it possible to have such a field?

[trq]

Yes its possible but you would need to maintained yourself. May I ask what the field is to be used for?

[fierdor]

Yeah...basically I am using it to randomise a selection in PHP:

My pseudocode:

1. Count the entries

2. rand(1,count)--Randomise a selection between 1 and the count

3. Select the content corresponding to that row number..

 

Hence in this algo it is necessary that my serial numbers be in sequence so that I can select the content...

I am game if there is some other algo which will make my life simple.

[fierdor]

I stumbled upon a solution while googling:

$random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1"));

But anyways is it possible to generate a sequence field programatically?

 

[Mchl]

I stumbled upon a solution while googling:

$random_row = mysql_fetch_row(mysql_query("select * from YOUR_TABLE order by rand() limit 1"));

But anyways is it possible to generate a sequence field programatically?

 

 

It's not a good solution. A better solution is presented in one of these presentations: http://www.phpfreaks.com/forums/index.php/topic,125105.msg959636.html#msg959636

 

Yes it is possible to generate such field, but it's usually not worth it. Especially if your table gets large, and you need to reorder thousands/millions of rows.

[fierdor]

Thank you!

That was an interesting read...He's stated 4 alternative solutions...

However i dont think my row count is going to increase beyond ,say 50 or 100...

So I assume the performance benefit will not be so noticeable,right?

But that said, I will keep this in mind for future projects..In fact that entire presentation seems to be quite useful..

[Mchl]

Yeah, for 100 rows you can safelu do ORDER BY RAND()