Code Summary of MySQL paging query for common stored procedures

This article mainly tells you about the general Stored Procedure of querying MySQL by page. We did not provide the general stored procedure for transferring SQLServer to MySQL some time ago. In the spirit of sharing with you, we will provide you with this MySQL paging query General stored procedure. Assume that the database used is guestbook:

 
 

  
  
Use guestbook;
  
  
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 Prima (the most complete virtual host Management System) ry_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', Prima (the most perfect VM management system) ry_field, sTemp );
  
  
Set sSql = concat (sSql, '(', Prima (the most perfect VM management system) ry_field, ')', 'from (select ');
  
  
Set sSql = concat (sSql, '', Prima (the most perfect VM management system) ry_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', Prima (the most complete virtual host Management System) ry_field, sTemp );
  
  
Set sSql = concat (sSql, '(', Prima (the most perfect VM management system) ry_field, ')', 'from (select ');
  
  
Set sSql = concat (sSql, '', Prima (the most perfect VM management system) ry_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;
 
 

It can be stored as a database script and then imported using commands:

MySQL (best combination with PHP)-u root-p <pageResult. SQL;

Call: call prc_page_result (1, "*", "Tablename", "", "columnname", 1, "PKID", 25 );

The above content is an introduction to the MySQL paging query common stored procedures. I hope you will get some benefits.