MySQL page 1

Test Table

Create Table if not exists 'test '(
'Id' int (11) not null auto_increment,
'Sort 'int (11)
'Title' varchar (50 ),
Primary Key ('id ')
) Engine = InnoDB default charset = gb2312 auto_increment = 1;

Stored ProcedureCode

delimiter $;
drop procedure if exists 'SP _ page' $
Create procedure 'SP _ page' (
In _ pagecurrent int, /* Current page */
In _ pagesize int,/* Number of records per page */
In _ ifelse varchar (1000 ), /* display field */
In _ Where varchar (1000),/* condition */
In _ order varchar (1000) /* sort */
)
comment 'paging store'
begin
If _ pagesize <= 1 then
SET _ pagesize = 20;
end if;
If _ pagecurrent <1 then
SET _ pagecurrent = 1;
end if;
set @ strsql = Concat ('select ', _ ifelse, 'from', _ Where, '', _ order, 'limit', _ pagecurrent * _ pagesize-_ pagesize, ',', _ pagesize );
prepare stmtsql from @ strsql;
execute stmtsql;
deallocate prepare stmtsql;

Set @ strsqlcount = Concat ('select count (1) as Count from', _ Where);/* count (1) this field is preferably the primary key */
Prepare stmtsqlcount from @ strsqlcount;
Execute stmtsqlcount;
Deallocate prepare stmtsqlcount;
End $
Delimiter; $

Call Code

Call 1 call sp_page (1, 3, '*', 'test', 'order by id desc ');

Call Example 2 call sp_page (1, 3, '*', 'test where sort = 1', 'order by id desc ');

Call Example 3 call sp_page (1, 3, 'Id, title', 'test where sort = 1', 'order by id desc ');