mysql自定义函数有关问题。

mysql自定义函数问题。。。

SQL code

CREATE FUNCTION getTargetFloor(message VARCHAR(255),appID INT,tID INT) RETURNS INTBEGIN DECLARE floor INT; DECLARE id INT; SET floor = 0; SET id = 0;    SELECT `id` INTO id FROM `k`.`comment` WHERE `tid`=tID AND `appid`=appID ORDER BY `id` LIMIT floor,1; END IF; RETURN(id);END

错误提示如下：

[Err] 1064 - 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 'floor,1;
 END IF;
 RETURN(id);
END' 


正确的应该怎么写呢？


[解决办法]
DELIMITER $$
CREATE FUNCTION getTargetFloor(message VARCHAR(255),appID INT,tID INT) RETURNS INT
BEGIN
 DECLARE `floor` INT;
 DECLARE Aid INT;
 SET `floor` = 0;
 SET Aid = 0;
SELECT `id` INTO Aid FROM `k`.`comment` WHERE `tid`=tID AND `appid`=appID ORDER BY `id` LIMIT FLOOR,1;
 RETURN Aid;
END$$
DELIMITER ;
[解决办法]
类似这样的还是用存储过程比较好。
[解决办法]

探讨

找到答案了，原来在limit后面不能用变量。

http://topic.csdn.net/u/20100105/16/2dc92381-2b1c-4362-bdde-eb54fa257a86.html

[解决办法]
你预备语句放到存储过程的用法都错了。

SQL code

mysql> use test;Database changedmysql> delimiter $$mysql> drop procedure if exists getId$$Query OK, 0 rows affected (0.00 sec)mysql> create procedure getId(in appID int,in tID int,in lim int)    -> begin    -> set @sql=concat('select id into @iid from comment  WHERE appid= ',appID,' AND tid= ',tID,' ORDER BY id LIMIT ',lim,',1');    -> PREPARE stmt from @sql;    -> EXECUTE stmt;    -> end ;$$Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> CALL getID(25,3399,0);Query OK, 0 rows affected (0.00 sec)mysql> SELECT @iid;+--------+| @iid   |+--------+| 144000 |+--------+1 row in set (0.00 sec)mysql>
[解决办法]
SQL code
DELIMITER $$CREATE FUNCTION getTargetFloor(message VARCHAR(255),appID INT,tID INT) RETURNS INTBEGIN DECLARE `floor` INT; DECLARE Aid INT; SET `floor` = 0; SET Aid = 0;  SELECT `id` INTO Aid FROM `k`.`comment` WHERE `tid`=tID AND `appid`=appID ORDER BY `id` LIMIT FLOOR,1; RETURN Aid;END$$DELIMITER ; 

 

查看更多 下一篇