Using variables as OFFSET in SELECT statments inside mysql's stored functions

Question

I'm quite new to subject of writting stored function for mySQL database, hence i'm not sure if what i'm trying to do here is possible at all.

I need a function that will return a column from random row from a table. I don't want to use ORDER BY RAND() method to do that, rather i would do this like this:

DECLARE MAX_COUNT INT DEFAULT 120000;
DECLARE rand_offset INT;
DECLARE str_rnd_word VARCHAR(255);
SET rand_offset = FLOOR((RAND() * MAX_COUNT));

SELECT word INTO str_rnd_word FROM all_words LIMIT 1 OFFSET rand_offset ;

RETURN str_rnd_word;

MySQL throws an error upon creating function with body like that. But when I use hard-coded number as OFFSET it works just fine.

Can someone shed some light on the subject please.

I'm running MySQL 5.0.45 on windows box.

Thanks

Cyan

Can you explain why "I don't want to use ORDER BY RAND() method to do that" ? – Slotter

Because the ORDER BY RAND() method is terrible for performance. Note that he has 120,000 rows in his table. – Supercool

well, you can do select like this: SELECT word FROM all_words ORDER BY RAND() LIMIT 1; and what you'll get is a random word from all_words table. This could work for me, but it's VERY slow on large tables. I need to deal with a table with 120000 rows. BTW this number is fixed and won't change. – Cyan

Answer 1

In MySQL before 5.5, you can't put a variable into the LIMIT clause in MySQL stored procedures. You have to interpolate it into a string and then execute the string as a dynamic query.

SET rand_offset = FLOOR(RAND() * (SELECT COUNT(*) FROM all_words));
SET @sql = CONCAT('SELECT word INTO str_rnd_word FROM all_words LIMIT 1 OFFSET ', rand_offset);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Supercool

As of MySQL 5.5.6, one can use variable LIMIT in SP. See Bug 11918. – Surfboarding