mysql分页通用存储过程

最新推荐文章于 2025-09-09 12:51:58 发布

转载最新推荐文章于 2025-09-09 12:51:58 发布 · 245 阅读

文章标签：

#mysql #数据库

DataBase 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一种使用MySQL存储过程实现复杂分页查询的方法。该方法通过动态SQL语句构造来适应不同的排序、条件和分页需求，适用于需要高级分页功能的应用场景。

use mysql;
drop procedure if exists prc_page_result
create procedure prc_page_result (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
begin
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);

if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if;

if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');
end if;
else
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
end;