Four mysql paging stored procedure instances-PHP Tutorial

Four mysql paging stored procedure instances. Four mysql paging stored procedure instances this article collects the code of four mysql paging stored procedure instances, there are efficient paging stored procedures and entry-level and general stored procedure paging code four mysql paging stored procedure instances this article collected four mysql paging stored procedure instance code, there are efficient paging stored procedures and entry-level and general stored procedure paging code. if you are learning the mysql paging stored procedure, come in and check it out.

Four mysql tutorial paging stored procedure instances
This article collects four mysql paging stored procedure instance codes, including efficient paging stored procedures and entry-level and general stored procedure paging code, if you are learning the mysql paging stored procedure, let's take a look.
Mysql test version: 5.0.41-community-nt
/*************************************** **************
Mysql paging stored procedure
Wu jian2009-02 2
**************************************** *************/
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),/* sorting condition (including the order keyword, which can be empty )*/
In p_where_string varchar (1024),/* where condition (contains the where keyword, which can be empty )*/
Out p_out_rows int/* Total number of output records */

)
Not deterministic
SQL security definer
Comment 'Paging stored process'

Begin

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

/* Construct the 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

The basic principle of stored procedure paging: We first temporarily store the key fields of the found record set (search condition _ whereclause and arrangement condition _ orderby are supported) to the temporary table, then build a real record set output.

Create procedure 'mysqltestuser _ select_pageable '(
_ Whereclause varchar (2000), -- search criteria
_ Orderby varchar (2000), -- sorting condition
_ Pagesize int, -- number of records per page
_ Pageindex int, -- current page number
_ Docount bit -- flag: Statistical data/output data
)
Not deterministic
SQL security definer
Comment''
Begin
-- Define a temporary key field table
Drop table if exists _ temptable_keyid; -- delete a temporary table.
Create temporary table _ temptable_keyid
(
Userid int
) Type = heap;

-- Construct dynamic SQL statements and output the id set of key keywords
-- Search condition
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 to insert the id record to the temporary table
Set @ SQL = concat ('Insert into _ temptable_keyid (userid) ', @ SQL );
Prepare stmt from @ SQL;
Execute stmt;
Deallocate prepare stmt;
-- Id set of key [end]

-- Below is the output
If (_ docount = 1) then -- Statistics
Begin
Select count (*) as recordcount from _ temptable_keyid;
End;
Else -- output record set
Begin
-- Calculate the start position of the record
Set @ startpoint = ifnull (_ pageindex-1) * _ pagesize, 0 );
Set @ SQL = 'SELECT .*
From mysqltestuser
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 ddl of the mysqltestuser table is as follows:

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 some data:
Insert into 'mysqltestuser' ('userid', 'name', 'chinesename', 'registerdatetime ', 'jf', 'Description') values
(1, 'xu1', 'www .aimeige.com.cn ', '2017-03-29 12:54:41', 2007, 'scription1 '),
(2, 'xuu2 ', 'www. bKjia. c0m', '2017-03-29 12:54:41', 2007, 'scription2 '),

Test the stored procedure call:

-- Method prototype 'mysqltestuser _ select_pageable' (condition, sorting order, number of records per page, page number, statistics)
-- Call 'mysqltestuser _ select_pageable' (_ whereclause, _ orderby, _ pagesize, _ pageindex, _ docount)

-- Statistical data
Call 'mysqltestuser _ select_pageable' (null, null, 1)
-- Output data, no restrictions, 10 Records/page, first page
Call 'mysqltestuser _ select_pageable' (null, null, 10, 1, 0)
-- Output data, condition restriction, arrangement, 10 Records/page, first page
Call 'mysqltestuser _ select_pageable' ('chinesename like ''% Fei 3% ''', 'userid asc ', 10, 1, 0)

A mysql. net method

Mysql + asp tutorial. net is used to write websites. since mysql already supports stored procedures, it is necessary to use stored procedures for common things such as paging!
However, I found some records on the internet and found that they all have a feature-that is, the total number of records cannot be transferred. I just want to study it myself. Finally, it was achieved, and the efficiency may not be very good, but I also think it is good. Paste the code directly: It is also a record for you to learn 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 number of records
Set @ limitstart = (m_pageno-1) * m_perpagecnt;
Set @ limitend = m_perpagecnt;
Set @ sqlcnt = concat ('SELECT count (1) into @ pagecnt from ', m_table); -- this statement is critical to obtain 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 4

In the spirit of sharing, the general stored procedure of mysql is provided for you to query the general stored procedure by mysql page. 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 varchiar (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;
It can be stored as a database script and then imported using commands:

Mysql-u root-p <pageresult. SQL;

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

*/
?>

Examples of paging stored procedures this article collects the code of four mysql paging stored procedure instances, including efficient paging stored procedures and entry-level and general paging stored procedure code...