通用的存储过程分页
参数
1.当前页码(必填)
2.每页大小(必填)
3.任意表的表名(必填)
4.任意表的查询列’id,name,age,sex’(可选)
5.任意查询条件’age > 35’(可选)
6.任意的排序列’age’(可选)
7.任意的排序类型(asc/desc)‘asc’(可选)
8.总记录数
9.总页数
存储函数
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_paging;
CREATE PROCEDURE sp_paging (
IN currentpage INT,
IN pagesize INT,
IN tname VARCHAR ( 500 ),
IN selections VARCHAR ( 5000 ),
IN conditions VARCHAR ( 5000 ),
IN sortcolumn VARCHAR ( 500 ),
IN sortOrder CHAR ( 4 ),
OUT totalnum INT,
OUT totalpage INT
) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '存储过程分页'
BEGIN
# 临时变量,标记开始查询位置
DECLARE vstart INT DEFAULT 0;
# 设置查询的起始位置
SET vstart = ( currentpage - 1 ) * pagesize;
# 判断是否填写查询列,若未填写则默认使用*
IF selections IS NULL
OR selections = '' THEN
SET selections = '*';
END IF;
# 判断是否填写查询条件
IF conditions IS NULL
OR conditions = '' THEN
SET conditions = '1=1';
END IF;
# 判断是否填写排序列,若未填写则使用默认的排序列'_id'(需要事先约定好)
IF sortcolumn IS NULL
OR sortcolumn = '' THEN
SET sortcolumn = '_id';
END IF;
# 判断是否给定正确的排序方式,如未给定或者不符合要求,则默认使用ASC(升序)
IF lower( sortOrder ) != 'ASC'
AND lower( sortOrder ) != 'DESC' THEN
SET sortorder = 'ASC';
END IF;
# SELECT * FROM employee WHERE age > 35 ORDER BY age DESC LIMIT 0,5;
SET @vsql = concat(
'SELECT ',
selections,
' FROM ',
tname,
' WHERE ',
conditions,
' ORDER BY ',
sortcolumn,
' ',
sortorder,
' LIMIT ',
vstart,
',',
pagesize
);
# 对 sql 语句预编译
PREPARE stmt
FROM
@vsql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
# 总记录数
SET @vsql = concat( 'SELECT COUNT(*) into @tnum FROM ', tname, ' WHERE ', conditions );
PREPARE stmt
FROM
@vsql;
EXECUTE stmt;
# 将临时变量中的数据赋值给输出参数
SET totalnum = @tnum;
SET totalpage = CEILING( totalnum / pagesize );
DEALLOCATE PREPARE stmt;
END $$;
DELIMITER;
测试
call sp_paging(1,100,'t_test',null,null,'id','asc',@tn,@tp);
select @tp,@tn;