11

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

CountZero
  • 2,844
  • 4
  • 22
  • 20
  • Can you explain why "I don't want to use ORDER BY RAND() method to do that" ? – Mitch Wheat Nov 19 '08 at 00:58
  • 2
    Because the ORDER BY RAND() method is terrible for performance. Note that he has 120,000 rows in his table. – Bill Karwin Nov 19 '08 at 01:07
  • 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. – CountZero Nov 19 '08 at 01:08

1 Answers1

9

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;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • As of MySQL 5.5.6, one *can* use variable `LIMIT` in SP. See [Bug 11918](http://bugs.mysql.com/bug.php?id=11918). – bishop Jun 10 '16 at 13:17