Jansfer – 长风破浪会有时，直挂云帆济沧海

delimiter $$
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;
$$
delimiter;
可以存储为数据库脚本，然后用命令导入：

mysql -u root -p < pageResult.sql;

调用：call prc_page_result(1, \”*\”, \”Tablename\”, \”\”, \”columnname\”, 1, \”PKID\”, 25);