MySql中用存储过程做多条件分页操作

48 阅读 0 评论 32 点赞

我是靠谱客的博主重要花卷，最近开发中收集的这篇文章主要介绍MySql中用存储过程做多条件分页操作，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

要用任意的查询条件，任意的排序列和排序类型来对任意表的任意的列进行查询，则输入参数必须要有表名称，要查询的列名称(或者列名称的列表)，查询条件，排序列的名称，排序的排序类型(降序desc或升序asc)，另外，还需传入需要显示的当前页的页数和每页的大小(即每页需要显示的数据条数)，也可通过该查询的存储过程传出查询得到的数据总条数和总页数。

CREATE PROCEDURE pro_page2
(
vtable_name VARCHAR ( 20 ),
#任意表
必填
vclumn_name VARCHAR ( 30 ),
#任意的查询列 可选
vtiaojian VARCHAR ( 50 ),
#任意查询条件 可选
sortcolumn VARCHAR ( 20 ),
#任意的排序列 可选
sorttype VARCHAR ( 4 ),
#任意的排序类型（asc,DESC） 可选
currentPage INT,
#当前页码 必填
recordNum INT,
#页面大小 必填
OUT countPage INT,
#符合条件数据的总页数
OUT countNum INT
#符合条件的数据的总条数 
)

写好了存储过程头部的参数准备部分，就要正式做查询了，不过，在查询之前，先要对传入的参数进行预处理，在输入参数中表名称，当前页的页数和每页的大小为必须传入的查询参数，而要查询的列名称，查询条件，排序列的名称，排序的排序类型则为可选的输入参数，即可传入具体的值，也可为null值。

查询列为空时，则默认为对所有的列进行查询，即其值可为" * " ，查询条件为空时，则默认为对所有的数据进行查询，即其值可为恒成立的" 1=1 "，排序列为空时，则可默认为按主键即按每个表的id列进行排序，排序类型为空时，可默认为按降序排序。

BEGIN
#设置查询的起始位置
DECLARE
startNum INT;
#判断是否填写查询列,查询条件,排序列,排序类型
IF
vclumn_name IS NULL
OR vclumn_name = '' THEN
SET vclumn_name = '*';
END IF;
#判断是否填写查询条件
IF
vtiaojian IS NULL
OR vtiaojian = '' THEN
SET vtiaojian = '1=1';
END IF;
#判断是否填写排序列,若未填写则使用默认的排序列“_id”,需要事先约定好
IF
sortcolumn IS NULL
OR sortcolumn = '' THEN
SET sortcolumn = '_id';
END IF;
#判断是否填写排序类型,若未给定或者不符合要求，则默认使用升序（ASC）
IF
lower( sorttype ) <> 'asc'
AND lower( sorttype ) <> 'desc' THEN
SET sorttype = 'asc';
END IF;

要把这些参数都用到select 语句中，不能直接将查询语句写成select clumn_name from tablb_name where limit ,这样的话，字符串" tablb_name "会被当做是一个表的表名，字符串"clumn_name"会被当做是tablb_name这个表中的某一个列的列名，其余的参数也是如此，不是将参数传入select语句中，而是取其字面的意思。所以，要将参数传入select语句中，需用concat()函数对select语句进行连接，然后对sql语句进行预编译，再执行其预处理对象。

 SET startNum =(
currentPage - 1
)* recordNum;
SET @SQL = concat(
"select ",
vclumn_name,
" from ",
vtable_name,
" where ",
vtiaojian,
" order by ",
sortcolumn,
" ",
sorttype,
" limit ",
startNum,
",",
recordNum
);
#对sql语句进行预编译
PREPARE stmt
FROM
@SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

对于输出参数的获取要用一个临时变量将其值存储起来，再将该临时变量的值赋给输出参数。

SET @sql2 = concat( "select count(*) into @temptotalnum from ", vtable_name, " where ", vtiaojian );
PREPARE stmt2
FROM
@sql2;
EXECUTE stmt2;
SET countNum = @temptotalnum;
#将临时变量中的数据赋值给输出参数
DEALLOCATE PREPARE stmt2;
SET countPage = CEILING( countNum / recordNum );
END;