问题场景
尝试编写mysql函数对记录中的数据排序完后,根据需求取第几条数据时,sql中的limit @变量,1 语法一直不通过编译。
报错信息如下:
[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 '@N,1
问题原因
limit n,m语句语法要求是,后面的两个参数必须为整数常量,不可以为变量。所以在函数中select * from a order by xx limit @变量,1就会函数编译不通过。
解决方法
1、使用整数常量
首先根据函数参数接收一个动态的整数参数,如果整数参数不需要运算那直接放在limit后面。
例如:当select test(1);时就会返回select xxx limit 1,1的数据
CREATE FUNCTION test(N INT) RETURNS double
BEGIN
RETURN select distinct gz from gzb order by gz desc limit N,1;
END
如果函数参数需要参与运算后才能得出实际值,也是使用常量,因为limit (n-1),1也是会编译不通过的。因为无论是2-1或者n-1都是变量。所以我们得把值提前处理变成常量。
例如:
CREATE FUNCTION test(N INT) RETURNS double
BEGIN
set cl = N-1
RETURN select distinct gz from gzb order by gz desc limit cl,1;
END
这样就不会报错,因为cl的值是已经通过n-1得到了才执行sql,而limit (n-1),1
或者set @cl = n-1;limit @cl,1
都是等到sql执行后才计算值,所以就会编译不通过。
2、编写存储过程+预编译
在mysql-5.0.7以后的版本,更新了可以预处理sql语句,在处理的时候再放入变量执行。
CREATE PROCEDURE test(in n INT)
BEGIN
set @mmsql = 'select distinct gz from gzb order by gz desc limit ?,1';
set @cl = n-1;
prepare exce from @mmsql;
EXECUTE exce USING @cl;
DEALLOCATE prepare exce; //删除预编译语句
END
或者直接使用select into进行变量赋值,根据b表进行循环查或插入操作
drop procedure if exists test;
CREATE PROCEDURE test(in n INT)
BEGIN
declare i int;
set i = 0;
select count(1) into @j from b;
while i < @j do
//查询或插入语句
end while
END
提示
声明变量的两种类型和范围
-- 局部变量
--Declare 变量名 类型 default 默认值;
declare N int defaul 0; -- 声明变量名为N的整型变量,默认值为0
-- 全局变量
-- set @变量名 = 值
set @N = 'xxx'; -- 声明全局变量@N,值为字符串xxx
存储过程语法
调用存储过程: call 存储名称(传参);
查看所有存储过程:show procedure status;
查看存储过程创建语句:show create procedure 存储名称;
删除存储过程:drop procedure 存储名称;
create procedure 名称(in|out|inout 参数名 参数类型) -- 多个参数使用逗号分隔
begin
-- sql语句;
end
-- in 代表参数的值从用户输入得到(默认为 in)
-- out 代表参数的值会向用户输出,但是存储过程中,传入该参数的值为NULL
-- inout 既可以接收参数,也可以输出参数
-- 例如:
set @a = 1;
set @b = 2;
set @c = 3;
select @a,@b,@c; -- 1,2,3
create procedure demo(a int, out b int, inout c int)
begin
select a,b,c; -- 1,NULL,3
set a = 10;
set b = 10;
set c = 10;
select a,b,c; -- 10,10,10
end
call demo(@a,@b,@c);
select @a,@b,@c; -- 1,10,10
执行完后,很明显@b,@c都被改变了
函数语法
调用函数: select 函数名称(传参);
查看所有自定义函数:show function status;
查看函数创建语句:show create function 函数名称;
删除函数:drop function 函数名称;
CREATE FUNCTION 函数名称(参数名 参数类型) RETURNS 返回值类型
begin
-- xxxx
end
-- 例如:输出1+2+...+n的结果
create function Accumulate(n int) returns int
begin
declare i int default 1;
declare sum int default 0;
while i <= n do
set sum = sum + i;
set i = i + 1;
end while;
return sum;
end
-- 或者使用loop和leave进行循环和跳出循环实现累加
create function Accumulate(n int) RETURNS int
begin
declare i int default 1;
declare sum int default 0;
add_num:LOOP
set sum = sum + i;
set i = i + 1;
IF i = n + 1 THEN LEAVE add_num;
END IF;
END LOOP add_num;
return sum;
end
-- 调用函数
select Accumulate(5); -- 15 = 1 + 2 + 3 + 4 + 5