[oclug]MySQL question

[Rod Giffin]

On Sun, 2003-01-19 at 21:42, Ian Wormsbecker wrote:
> It seems as though you are trying to explain how you can determine if a value 
> is NULL in a tuple from within a program, for instance a perl script. I am 
> not familiar with perl so cannot comment on that, but I should hope that NULL 
> is not 0.00 as 0.00 is a valid float value. 

Well, no I'm not trying to explain that, and we're making this more
complicated than it needs to be.

I'm saying that there is a difference in SQL between NULL on one hand
and "" or zero on the other that programmers unfamiliar with SQL do not
always expect.  Explanation and discussion is at both the link I
provided, and the link you provided.

At any rate, there is a way to write the query so that a random record
will be chosen, even if one of the current records is deleted, or
additional records are added, without having to modify the SQL
statement.  I believe it has already been suggested.

SELECT sponsor_id, name FROM sponsor ORDER BY RAND() LIMIT 1;

I would go one step further though, and use a "" to indicate an empty
name in order to avoid any confusion in the program code that is going
to be handling the result set.  If I had a query to return a valid
random sponsor_id and a valid name when it ran, I'd use: 

SELECT sponsor_id, name FROM sponsor WHERE name <> "" ORDER BY rand()
LIMIT 1;

Rod.