mysql存储过程:在limit语句中使用声明的vars返回错误 [英] mysql stored procedure: using declared vars in a limit statement returns an error

问题描述

我有以下代码:

delimiter ;

DROP PROCEDURE IF EXISTS ufk_test;
delimiter //
CREATE PROCEDURE ufk_test(IN highscoreChallengeId INT UNSIGNED)
BEGIN
DECLARE vLoopOrder INT UNSIGNED DEFAULT 5;
DECLARE vLoopLimit INT UNSIGNED DEFAULT 10;
select * from fb_user LIMIT vLoopOrder,vLoopLimit;
END//

delimiter ;

Mysql返回以下错误:

Mysql returns the following error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'vLoopOrder,vLoopLimit;
END' at line 11

似乎我不能在LIMIT语句中使用声明的变量.还有其他方法可以克服吗?

it seems that I cannot use declared variables in a LIMIT statement. is there any other way to overcome this ?

这当然是一个简单的示例，在这里我可以放置静态数字，但是我需要知道是否可以以任何方式在LIMIT中使用任何种类的变量.

of course this is a simple example, here i could just put static numbers but I need to know if it's possible in any way to use any kind of variables with LIMIT.

谢谢

推荐答案

我使用类似的东西:

SET @s = CONCAT('SELECT * FROM table limit  ', vLoopOrder ', ', vLoopLimit); 
PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

这篇关于mysql存储过程:在limit语句中使用声明的vars返回错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！