Four mysql paging stored procedure examples_PHP tutorial

Four examples of mysql paging stored procedures This article collects four MySQL paging stored procedure example codes, including efficient paging stored procedures as well as entry-level and general stored procedure paging codes. If you are learning MySQL paging stored procedures, come and take a look.

Four mysql tutorials with paging stored procedure examples
This article collects four MySQL paging stored procedure example codes, including efficient paging stored procedures as well as entry-level and general stored procedure paging codes. If you are learning MySQL paging stored procedures, come and take a look.
mysql test version: 5.0.41-community-nt
/***************************************************** *
mysql paging stored procedure
Wu Jian 2009-07-02
*************************************************** ***/
drop procedure if exists pr_pager;
create procedure pr_pager(

in p_table_name varchar(1024), /*table name*/
in p_fields varchar(1024), /*Query field*/
in p_page_size int, /*Number of records per page*/
in p_page_now int, /*Current page*/
in p_order_string varchar(128), /*Sort conditions (including order keyword, can be empty)*/
in p_where_string varchar(1024), /*where condition (contains where keyword, can be empty)*/
out p_out_rows int int /*Total number of output records*/
 
)
Not deterministic
sql security definer
Comment 'Paging stored procedure'
 
begin

/*Define variables*/
declare m_begin_row int default 0;
declare m_limit_string char(64);

/*Construction statement*/
Set m_begin_row = (p_page_now - 1) * p_page_size;
set m_limit_string = concat(' limit ', m_begin_row, ', ', p_page_size);
 
set @count_string = concat('select count(*) into @rows_total from ', p_table_name, ' ', p_where_string);
set @main_string = concat('select ', p_fields, ' from ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);

/*Preprocessing*/
Prepare count_stmt from @count_string;
execute count_stmt;
deallocate prepare count_stmt;
Set p_out_rows = @rows_total;

prepare main_stmt from @main_string;
execute main_stmt;
deallocate prepare main_stmt;
 
end

An efficient stored procedure paging code

Basic principle of stored procedure paging: We first temporarily store the key field of the found record set (supports input search condition _whereclause and arrangement condition _orderby) into a temporary table, and then construct the real record set output.

create procedure `mysqltestuser_select_pageable`(
​​​​​_whereclause​​varchar(2000),​​​​​​​​​​​​​​​​​​​​​​​​​​ ​​​​​_orderby​​varchar(2000),​​​​​​​​​​​​​​​​​​​​​​ _pagesize , int , -- Number of records per page
​ ​ _pageindex ​ int , ​ ​ -- Current page number
_Docount bit -logo: statistical data/output data
)
Not deterministic
sql security definer
                                                                                                                                                                                begin
-- Define key field temporary table
drop table if exists _temptable_keyid; -- Delete the temporary table, if it exists
create temporary table _temptable_keyid
(
userid int
)type=heap;

-- Build dynamic sql and output the id set of keyword keys
-- Search conditions

if (_whereclause is not null) and (_whereclause <> ' ') then
set @sql= concat(@sql, ' where ' ,_whereclause);
end if;

if (_orderby is not null) and (_orderby <> ' ') then
set @sql= concat( @sql , ' order by ' , _orderby);

-- Prepare id records to be inserted into the temporary table
set @sql=concat( 'insert into _temptable_keyid(userid) ', @sql);

execute stmt ;
deallocate prepare stmt;
-- key id collection [end]

-- The following is the output
if (_docount=1) then -- statistics

Select count (*) as recordcount from _temptable_keyid;
end;
else                                                                                                                                                                                                                                                                                                           begin
-Calize the starting point of the record record
set @startpoint = ifnull((_pageindex-1)*_pagesize,0);
Set @sql = 'select a.*
          from           mysqltestuser   a
inner join _temptable_keyid b
on a.userid =b.userid ';

  set   @sql=concat(@sql, "     limit     ",@startpoint, "   , ",_pagesize);
                  prepare   stmt   from   @sql;
  execute   stmt   ;
  deallocate   prepare   stmt;
          end;
end   if;

  drop   table   _temptable_keyid;
end;

下面是mysqltestuser表的ddl:

create   table   `mysqltestuser`   (
    `userid`   int(11)   not   null   auto_increment,
    `name`   varchar(50)   default   null,
    `chinesename`   varchar(50)   default   null,
    `registerdatetime`   datetime   default   null,
    `jf`   decimal(20,2)   default   null,
    `description`   longtext,
    primary   key     (`userid`)
)   engine=innodb   default   charset=gb2312;

插入些数据:
insert   into   `mysqltestuser`   (`userid`,   `name`,   `chinesename`,   `registerdatetime`,   `jf`,   `description`)   values  
    (1, 'xuu1 ', 'www.aimeige.com.cn ', '2007-03-29   12:54:41 ',1.5, 'description1 '),
    (2, 'xuu2 ', 'www.bKjia.c0m ', '2007-03-29   12:54:41 ',2.5, 'description2 '),

存储过程调用测试:

--   方法原型     `mysqltestuser_select_pageable`(条件,排列顺序,每页记录数,第几页,是否统计数据)
--   call   `mysqltestuser_select_pageable`(_whereclause   ,_orderby   ,_pagesize   ,_pageindex   ,   _docount)

--   统计数据
call   `mysqltestuser_select_pageable`(null,   null,   null,   null,   1)
--   输出数据,没条件限制,10条记录/页,第一页
call   `mysqltestuser_select_pageable`(null,   null,   10,   1,0)
--   输出数据,条件限制,排列,   10条记录/页,第一页
call   `mysqltestuser_select_pageable`( 'chinesename   like   ' '%飞3% ' ' ',   'userid   asc ',   10,   1,   0)

一款mysql .net的方法

mysql + asp教程.net来写网站，既然mysql已经支持存储过程了，那么像分页这么常用的东西，当然要用存储过程啦!
　　不过在网上找了一些，发现都有一个特点——就是不能传出总记录数，干脆自己研究吧。终于，算是搞出来了，效率可能不是很好，但是我也觉得不错了。贴代码吧直接：也算是对自己学习mysql的一个记录。
　　create procedure p_pagelist
　　(
　　m_pageno int ,
　　m_perpagecnt int ,
　　m_column varchar(1000) ,
　　m_table varchar(1000) ,
　　m_condition varchar(1000),
　　m_orderby varchar(200) ,
　　out m_totalpagecnt int
　　)
　　begin
　　set @pagecnt = 1; -- 总记录数
　　set @limitstart = (m_pageno - 1)*m_perpagecnt;
　　set @limitend = m_perpagecnt;
　　set @sqlcnt = concat('select count(1) into @pagecnt from ',m_table); -- 这条语句很关键，用来得到总数值
　　set @sql = concat('select ',m_column,' from ',m_table);
　　if m_condition is not null and m_condition <> '' then
　　set @sql = concat(@sql,' where ',m_condition);
　　set @sqlcnt = concat(@sqlcnt,' where ',m_condition);
　　end if;
　　if m_orderby is not null and m_orderby <> '' then
　　set @sql = concat(@sql,' order by ',m_orderby);
　　end if;
　　set @sql = concat(@sql, ' limit ', @limitstart, ',', @limitend);
　　prepare s_cnt from @sqlcnt;
　　execute s_cnt;
　　deallocate prepare s_cnt;
　　set m_totalpagecnt = @pagecnt;
　　prepare record from @sql;
　　execute record;
　　deallocate prepare record;
　　end

方法四

MySQL's universal stored procedure, in the spirit of sharing, dedicates this MySQL paging query universal stored procedure to everyone. Assume that the database tutorial 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 primary_field varchar(100),
in pagesize int
)
begin
declare stemp varchar(1000);
declare ssql varchar(4000);
declare order varchar(1000);
 
If asc_field = 1 then
​​​​​ set order = concat(order by, order_field, desc);
set stemp = <(select min;
else
          set order = 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, order, limit?);
        else
set ssql = concat(select, columns, from, tablename, order, 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, order);
set ssql = concat(ssql, limit, (currpage-1)*pagesize, ) as tabtemp), order);
                 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, order);
set ssql = concat(ssql, limit, (currpage-1)*pagesize, ) as tabtemp), order);
                 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;
Can be stored as a database script and then imported with the command:

mysql -u root -p < pageresult.sql;

Call: call prc_page_result(1, "*", "tablename", "", "columnname", 1, "pkid", 25);

*/
?>