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
错误提示如下:[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'
正确的应该怎么写呢?
解决方案 »
- 各位大神看一看吧,这个错误我调一天了。。。SQL的修改语句
- mysql phpmyadmin如何改变数据库的字符集??
- mysql的字段拆分问题
- 求教一SQL语句
- 谁能帮帮我...在线等..关于数据库建表的问题?
- 一个令人头疼的错误:can't open **.MYD , error 126
- 在MYSQL里如何把对两个表的SELECT语句的结果放在一个结果集里面?
- mysql5.5查询表结构时报错~急求解决办法
- mysql表字段查询
- 关于Navicat导入数据库后设置主键的问题
- 请教各位MySQL 是否可以将OS用户映射为database用户
- mysql 数据库被 drop database 数库库名,能恢复吗?
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
delimiter $$
DROP PROCEDURE IF EXISTS `getID`$$
CREATE PROCEDURE getID(IN appID INT, IN tID INT, IN floor INT)
BEGIN
DECLARE s VARCHAR(255);
DECLARE f INT;
SET f = floor - 1;
SET @mID = 0;
SET s = "SELECT `id` INTO @mID FROM `comment` WHERE `appid`=? AND `tid`=? ORDER BY `id` LIMIT ?,1;";
SET @ms = s;
PREPARE MY_STMT FROM @ms;
EXECUTE MY_STMT USING @appID, @tID, @f;
END
$$
delimiter ;
我写这样一个存储过程,却得不到@mID的值,为什么?附上表结构:
CREATE TABLE `comment` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT '留言id',
`tid` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '主题id',
`appid` mediumint(8) NOT NULL DEFAULT '0' COMMENT '应用接口分类id',
PRIMARY KEY (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;INSERT INTO `comment` VALUES (144000, 3399, 25);CALL getID(25,3399,0);
SELECT @mID;
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>
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 ;
下面的写法也是可以的。delimiter $$
DROP PROCEDURE IF EXISTS `getID`$$
CREATE PROCEDURE getID(IN appID INT, IN tID INT, IN floor INT)
BEGIN
DECLARE s VARCHAR(255);
DECLARE f INT;
SET f = floor - 1;
SET @appID= appID;
SET @tID= tID;
SET @f= f;
SET @mID = 0;
SET @ms = "SELECT `id` INTO @mID FROM `comment` WHERE `appid`=? AND `tid`=? ORDER BY `id` LIMIT ?,1;";
PREPARE STMT FROM @ms;
EXECUTE STMT USING @appID, @tID, @f;
END
$$
delimiter ;
RETURN Aid;