[mysql学习]存储过程分页-代码

DELIMITER $$

CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `cadb`.`Query_Pagination`(
IN _fields VARCHAR(2000),
IN _tables TEXT,
IN _where VARCHAR(2000),
IN _orderby VARCHAR(200),
IN _pageindex INT,
IN _pagesize INT,
IN _sumfields VARCHAR(200),/*增加统计字段2013-5-8 peaceli*/
OUT _totalcount INT ,
OUT _pagecount INT
)
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
/*
--名称：MYSQL版查询分页存储过程 by peace 2013-8-14
--输入参数：@fields -- 要查询的字段用逗号隔开
--输入参数：@tables -- 要查询的表
--输入参数：@where -- 查询条件
--输入参数：@orderby -- 排序字段
--输出参数：@page -- 当前页计数从1开始
--输出参数：@pagesize -- 每页大小
--输出参数：@totalcount -- 总记录数
--输出参数：@pagecount -- 总页数
*/
BEGIN
SELECT '--begin--';
SET @startRow = _pageSize*(_pageIndex -1);
SET @pageSize = _pageSize;
SET @rowindex = 0;
SET @strsql = CONCAT('select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,',_fields,' from ',_tables,CASE IFNULL(_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',_where) END,' order by ',_orderby,' limit ',@startRow,',',@pageSize);
SELECT CONCAT('strsql==',@strsql);
PREPARE strsql FROM @strsql;
EXECUTE strsql;
DEALLOCATE PREPARE strsql;
SET _totalcount = FOUND_ROWS();
SELECT CONCAT('_totalcount==',_totalcount);
SELECT CONCAT('_pageSize==',_pageSize);
IF (_totalcount <= _pageSize) THEN
SET _pagecount = 1;
ELSE
IF (MOD(_totalcount , _pageSize )> 0) THEN
SET _pagecount = FLOOR(_totalcount / _pageSize) + 1;
ELSE
SET _pagecount = _totalcount / _pageSize;
END IF;
END IF;
SELECT CONCAT('_pagecount==',_pagecount);
SELECT CONCAT('_sumfields==',_sumfields);
IF(IFNULL(_sumfields,'') <> '') THEN
SET @sumsql = contact('select ',_sumfields,' from ',_tables,CASE IFNULL(_where,'') WHEN '' THEN '' ELSE CONCAT(' where ',_where) END);
SELECT CONCAT('@sumsql==',@sumsql);
PREPARE sumsql FROM @sumsql;
EXECUTE sumsql;
DEALLOCATE PREPARE sumsql;
END IF;
SELECT '--end--';
END$$

DELIMITER ;





mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',1,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------+
| CONCAT('strsql==',@strsql) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 0,3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
| 1 | -1 | 110040114000736 | 2011年拉抚庄园哈斯图干红葡萄酒 | 69373266589 | 2014-02-09 00:00:00 | 北京玖美电子商务有限公司 | 北京润和恒安国际贸易有限公司 | 1 | 2204210000 |
| 2 | 1 | INSP20140909111 | 进口测试商品 | 69373266589 | 2014-05-12 00:00:00 | 测试 | 测试 | 1 | INSP20140909111-1 |
| 3 | 2 | BJ002 | 进口测试商品1 | 69373266590 | 2014-05-09 00:00:00 | 杭州滨江 | 杭州西湖 | 1 | hs001 |
+-----------+-----------+-----------------+--------------------------------+-------------+---------------------+--------------------------+------------------------------+----------+-------------------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-------

----------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql>
mysql>
mysql>

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',2,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 3,3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
| 1 | 4 | INSP2014 | 进口测试商品 | 69373266589 | 2014-05-12 00:00:00 | 测试1 | 测试 | 1 | INSP2014 |
| 2 | 6 | INSP201456565 | 出口测试商品 | 69373266591 | 2014-05-12 00:00:00 | 测试 | 测试1 | 2 | INSP201456565 |
| 3 | 7 | INSP201456577 | 出口测试商品1 | 69373266592 | 2014-05-12 00:00:00 | 测试 | 测试1 | 2 | INSP201456577 |
+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32

|
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql>
mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',2,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 3,3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
| 1 | 4 | INSP2014 | 进口测试商品 | 69373266589 | 2014-05-12 00:00:00 | 测试1 | 测试 | 1 | INSP2014 |
| 2 | 6 | INSP201456565 | 出口测试商品 | 69373266591 | 2014-05-12 00:00:00 | 测试 | 测试1 | 2 | INSP201456565 |
| 3 | 7 | INSP201456577 | 出

口测试商品1 | 69373266592 | 2014-05-12 00:00:00 | 测试 | 测试1 | 2 | INSP201456577 |
+-----------+-----------+---------------+---------------+-------------+---------------------+----------------+-----------+----------+---------------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',3
,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 6,3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+-------------+--------------+-------------+---------------------+----------------+-----------+----------+----------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+-------------+--------------+-------------+---------------------+----------------+---------

--+----------+----------+
| 1 | 8 | 34325353 | 茶叶 | 69373659805 | 2014-05-12 00:00:00 | 石峰龙井 | 代友 | 1 | HS343435 |
| 2 | 9 | 77777777 | Oliver护手霜 | 69373463197 | 2014-05-12 00:00:00 | 浙大创新院 | 浙大圆正 | 1 | 23232323 |
| 3 | 10 | 4545456677 | 韩国泡菜 | 69373463201 | 2014-05-12 00:00:00 | 浙大创新院 | 浙大圆正 | 1 | 7777777 |
+-----------+-----------+-------------+--------------+-------------+---------------------+----------------+-----------+----------+----------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',4,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 9,3 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+-------------+

--------------+-------------+---------------------+----------------+-----------+----------+---------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+-------------+--------------+-------------+---------------------+----------------+-----------+----------+---------+
| 1 | 11 | 6666666 | Oliver护手霜 | 69373463197 | 2014-05-12 00:00:00 | 浙大创新院 | 浙大圆正 | 1 | 7777777 |
| 2 | 12 | 123456 | 测试茶叶1 | 69373659806 | 2014-05-15 00:00:00 | 张三 | 李四 | 1 | 123456 |
| 3 | 13 | 00123 | 韩国泡菜 | 69373463201 | 2014-05-14 00:00:00 | 浙大创新院 | 浙大圆正 | 1 | 7777777 |
+-----------+-----------+-------------+--------------+-------------+---------------------+----------------+-----------+----------+---------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',10
,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,Inspe

ctDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 27,3 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
| 1 | 29 | 666666777888987 | PPloy 9090 | 69373266620 | 2014-05-27 00:00:00 | 999 | | 1 | 1234567893 |
| 2 | 30 | 666666777888987 | PPloy 9090 | 69373266620 | 2014-05-27 00:00:00 | dsfsdf | | 1 | 1234567893 |
| 3 | 31 | 123333222444556 | PPNooly 7878 | 69373266621 | 2014-05-27 00:00:00 | 21321 | | 1 | 1111222234 |
+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
3 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',11
,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CONCAT('strsql==',@strsql)

|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 30,3 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
| rownumber | InspectID | InspectCode | ProductName | TypeID | InspectDate | InDistributors | InAgentor | Category | HsCode |
+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
| 1 | 32 | 454567891234123 | PPNooly 7878 | 69373266621 | 2014-05-28 00:00:00 | 1221 | | 1 | 1111222234 |
| 2 | 33 | 123123123111111 | Jinkou009 | 69373397663 | 2014-06-10 00:00:00 | 11111 | | 1 | 1111112223 |
+-----------+-----------+-----------------+--------------+-------------+---------------------+----------------+-----------+----------+------------+
2 rows in set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql> CALL Query_Pagination('InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode','inspect','','InspectID',12
,3,'',@_totalcount,@_pagecount);
+-----------+
| --begin-- |
+-----------+
| --begin-- |
+-----------+
1 row in set

+-----------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------+
| CONCAT('strsql==',@strsql) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| strsql==select sql_calc_found_rows @rowindex:=@rowindex+1 as rownumber,InspectID,InspectCode,ProductName,TypeID,InspectDate,InDistributors,InAgentor,Category, HsCode from inspect order by InspectID limit 33,3 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Empty set

+-------------------------------------+
| CONCAT('_totalcount==',_totalcount) |
+-------------------------------------+
| _totalcount==32 |
+-------------------------------------+
1 row in set

+---------------------------------+
| CONCAT('_pageSize==',_pageSize) |
+---------------------------------+
| _pageSize==3 |
+---------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_pagecount==',_pagecount) |
+-----------------------------------+
| _pagecount==11 |
+-----------------------------------+
1 row in set

+-----------------------------------+
| CONCAT('_sumfields==',_sumfields) |
+-----------------------------------+
| _sumfields== |
+-----------------------------------+
1 row in set

+---------+
| --end-- |
+---------+
| --end-- |
+---------+
1 row in set

Query OK, 0 rows affected

mysql>