mysql自定义函数问题。。。
- SQL code
CREATE FUNCTION getTargetFloor(message VARCHAR(255),appID INT,tID INT) RETURNS INT BEGIN 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
欢迎大家阅读《mysql自定义函数有关问题。》,跪求各位点评，by 搞代码
错误提示如下：
[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 ;
——解决方案——————–
类似这样的还是用存储过程比较好。
——解决方案——————–
——解决方案——————–
你预备语句放到存储过程的用法都错了。
- SQL code
mysql> use test; Database changed mysql> 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 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 ;搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql自定义函数有关问题。
