How to Use General stored procedures to perform MySQL paging queries

The following articles mainly describe general stored procedures to perform correct operations on MySQL paging queries, if you are interested in performing the correct operations on the MySQL paging query, you can click the following article to view it. I hope you will find some benefits after browsing.

Some time ago, I did not provide a general stored procedure for transferring SQLServer to MySQL (the best combination with PHP). In the spirit of sharing, I would like to dedicate this MySQL paging query common stored procedure for everyone, assume that the database 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.