Four MySQL paging stored procedure Example _php tutorial

Four MySQL paging stored procedure examples This article collects four MySQL paging stored procedure instance code, has the efficient paging stored procedure as well as the entry-level and the common stored procedure pagination code, if you are learning the MySQL paging stored procedure to come in to see.

Four MySQL tutorial paging stored procedure Instance
This article collects four MySQL paging stored procedure instance code, has the efficient paging stored procedure as well as the entry level and the common stored procedure paging code, if you are learning the MySQL paging stored procedure to come in to see.
MySQL beta version: 5.0.41-community-nt
/*****************************************************
MySQL Paging stored procedure
Detections 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 condition (contains order keyword, can be empty) */
In p_where_string varchar (1024x768),/*where condition (contains the WHERE keyword, can be empty) */
Out P_out_rows INT/* Total OUTPUT record */

)
Not deterministic
SQL Security Definer
Comment ' Paging stored procedure '

Begin

/* Define variable */
DECLARE m_begin_row int default 0;
DECLARE m_limit_string char (64);

/* Construct 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_s Tring);

/* Pre-treatment */
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 paging code for stored procedures

The basic principle of the stored procedure paging: We first store the key field of the found recordset (which supports input lookup criteria _whereclause and _orderby) to a temporary table, and then build the true recordset output.

CREATE PROCEDURE ' Mysqltestuser_select_pageable ' (
_whereclause varchar (2000),--Find condition
_orderby varchar (2000),--sorting conditions
_pagesize int,--Number of records per page
_pageindex int,--current page
_docount bit--flag: Statistics/output data
)
Not deterministic
SQL Security Definer
Comment "
Begin
--Define the key field temp table
drop table if exists _temptable_keyid; --Delete the temporary table, if present
Create temporary table _temptable_keyid
(
UserID int
) Type=heap;

--Build dynamic SQL, output keyword Key ID collection
--Find conditions
Set @sql = ' Select UserID from Mysqltestuser ';
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);
End If;

--Prepare the ID record to insert into the temp table
Set @sql =concat (' INSERT INTO _temptable_keyid (userid) ', @sql);
Prepare stmt from @sql;
Execute stmt;
deallocate prepare stmt;
--Key's ID set [end]

--Here is the output
if (_docount=1) then--statistics
Begin
Select COUNT (*) as RecordCount from _temptable_keyid;
End
Else-Output record set
Begin
--Calculate the starting position of the 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

The following is the DDL for the Mysqltestuser table:

CREATE TABLE ' Mysqltestuser ' (
' userid ' int (one) not NULL auto_increment,
' Name ' varchar (default null),
' chinesename ' varchar default NULL,
' registerdatetime ' datetime default NULL,
' JF ' decimal (20,2) default NULL,
' Description ' Longtext,
Primary KEY (' UserID ')
) Engine=innodb default charset=gb2312;

Insert some data:
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 '),

Stored Procedure Call test:

--Method prototype ' mysqltestuser_select_pageable ' (conditions, order, number of records per page, pages, whether statistical data)
--Call ' mysqltestuser_select_pageable ' (_whereclause, _orderby, _pagesize, _pageindex, _docount)

--Statistical data
Call ' mysqltestuser_select_pageable ' (null, NULL, NULL, NULL, 1)
--output data, no condition limit, 10 records/page, first page
Call ' mysqltestuser_select_pageable ' (null, NULL, 10, 1,0)
--output data, condition limit, arrangement, 10 Records/page, first page
Call ' mysqltestuser_select_pageable ' (' chinesename like ' '% fly 3% ', ' userid ASC ', 10, 1, 0)

A method of MySQL. Net

mysql + ASP tutorial. NET to write a website, since MySQL already supports stored procedures, then like paging so common things, of course, the use of stored procedures!
But on the internet to find some, found that there is a feature--that is, can not transfer the total number of records, simply study it yourself. Finally, it is out, the efficiency may not be very good, but I also feel good. Stick to the code bar directly: It is also a record of their own learning 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; --Total Record Count
Set @limitstart = (m_pageno-1) *m_perpagecnt;
Set @limitend = m_perpagecnt;
Set @sqlcnt = Concat (' Select COUNT (1) to @pagecnt from ', m_table); --This statement is critical to get the total value
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

Method Four

MySQL's universal stored procedures, in the spirit of sharing, for everyone to dedicate this MySQL paging query common stored procedures, assume that the database tutorial for 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 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, Primary_field, stemp);
Set sSQL = Concat (sSQL, (, Primary_field,), from (select);
Set sSQL = Concat (sSQL, Primary_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, Primary_field, stemp);
Set sSQL = Concat (sSQL, (, Primary_field,), from (select);
Set sSQL = Concat (sSQL, Primary_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
Can be stored as a database script and then imported with a command:

Mysql-u Root-p < Pageresult.sql;

Invoke: Call Prc_page_result (1, "*", "tablename", "", "ColumnName", 1, "Pkid", 25);

*/
?>

http://www.bkjia.com/PHPjc/630787.html www.bkjia.com true http://www.bkjia.com/PHPjc/630787.html techarticle Four MySQL paging stored procedure examples This article collects four MySQL paging stored procedure instance code, has the efficient paging stored procedure as well as the entry-level and the common stored procedure pagination code ...

