Mysql 存储过程分页(AID)

               AID系统中要用到分页查询数据的功能，在网上查了几个，很多都很类似，下面我结合自己的数据实例编写的通用的分页查询。

              代码如下：

             

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `Pro_QueryByPageList`(
IN pageindex int,  
IN columns varchar(1000),
IN table_name varchar(100),
IN scondition varchar(1000),
IN pagesize int ,
IN pk_field varchar(100)

)
BEGIN

DECLARE sSql varchar(5000);
declare sOrder varchar(100);

set sOrder=concat(' order by ',pk_field,'');

if pageindex =1 then
    if scondition <> '' then
      set sSql=concat('select ',columns,' from ',table_name,' where ');
      set sSql=concat(sSql,scondition,sOrder,' limit ',pagesize);     
    else 
      set sSql=concat('select ',columns,' from ',table_name,sOrder,' limit ',pagesize);
    end if;
else
    if scondition <> '' then
        set sSql=concat('select ',columns,' from ',table_name,' where ');
        set sSql=concat(sSql,scondition,' and ',pk_field,' not in ','(select ',pk_field);
        set sSql=concat(sSql,' from (select ',pk_field,' from ',table_name,' where ',scondition,sOrder,' limit ',(pageindex-1)*pagesize,') as t )',sOrder);
        set sSql=concat(sSql,' limit ',pagesize);
     else
        set sSql=concat('select ',columns,' from ',table_name);
        set sSql=concat(sSql,' where ',pk_field,' not in ', '(select ',pk_field);
        set sSql=concat(sSql,' from (select ',pk_field,' from ',table_name,sOrder,' limit ',(pageindex-1)*pagesize,') as t )',sOrder);
        set sSql=concat(sSql,' limit ',pagesize);
    end if;
 end if;
    -- set strsql=sSql;
     set @sql=sSql;
     prepare stmt from @sql;
     execute stmt;
END

 

查询命令：

 

use aiddatabase;

call Pro_QueryByPageList(2,"*","tb_fullpic","",10,"FullPic_ID");

 

下面是系统中按钮的命令操作