在mysql的存储函数内部的SELECT语句中使用变量作为OFFSET [英] Using variables as OFFSET in SELECT statments inside mysql's stored functions

问题描述

我对编写用于mySQL数据库的存储函数的主题还很陌生，因此我不确定在这里尝试执行的操作是否完全可能.

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.

我需要一个函数，该函数将从表的随机行返回一列.我不想使用ORDER BY RAND()方法来执行此操作，而我会这样做:

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在创建具有此类主体的函数时引发错误.但是，当我使用硬编码的数字作为OFFSET时，它就可以正常工作.

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.

我正在Windows框上运行MySQL 5.0.45.

I'm running MySQL 5.0.45 on windows box.

谢谢

推荐答案

在5.5之前的MySQL中，您不能将变量放入MySQL存储过程的LIMIT子句中.您必须将其插入一个字符串中，然后作为动态查询执行该字符串.

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;

这篇关于在mysql的存储函数内部的SELECT语句中使用变量作为OFFSET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！