[共享]我的高效mysql分页存储过程...

[xuu27]

总是习惯了mssql上面的存储过程分页,然后结合AspNetPager分页控件(http://www.webdiyer.com/AspNetPager/default.aspx),这样就完成了记录的分页显示；

由于最近用到mysql,由于mssql,mysql两者的表达语法和语法约束上的区别，导致写起mysql上的分页版本有点“复杂”。经过查找一番资料后终于把它“复制”了。

存储过程分页的基本原理:我们先对查找到的记录集(支持输入查找条件_WhereClause和排列条件_OrderBy)的key字段临时存放到临时表,然后构建真正的记录集输出。

CREATE PROCEDURE `mysqltestuser_SELECT_PageAble`(
_WhereClause VARCHAR(2000), -- 查找条件
_OrderBy VARCHAR(2000), -- 排序条件
_PageSize INT , -- 每页记录数
_PageIndex INT , -- 当前页码
_DoCount BIT -- 标志:统计数据/输出数据
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
-- 定义key字段临时表
DROP TABLE IF EXISTS _TempTable_KeyID; -- 删除临时表,如果存在
CREATE TEMPORARY TABLE _TempTable_KeyID
(
userid INT
)TYPE=HEAP;

-- 构建动态的sql，输出关键字key的id集合
-- 查找条件
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;

-- 准备id记录插入到临时表
set @sql=concat('insert into _TempTable_KeyID(userid)', @sql);
PREPARE stmt FROM @sql;
EXECUTE stmt ;
DEALLOCATE PREPARE stmt;
-- key的id集合 [end]

-- 下面是输出
IF (_DoCount=1) then -- 统计
BEGIN
SELECT COUNT(*) AS RecordCount FROM _TempTable_KeyID;
END;
ELSE -- 输出记录集
BEGIN
-- 计算记录的起点位置
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','张飞1','2007-03-29 12:54:41',1.5,'description1'),
(2,'xuu2','张飞2','2007-03-29 12:54:41',2.5,'description2'),
(3,'xuu3','张飞3','2007-03-29 12:54:41',3.5,'description3'),
(4,'xuu4','张飞4','2007-03-29 12:54:41',4.5,'description4'),
(5,'xuu5','张飞5','2007-03-29 12:54:41',5.5,'description5'),
(6,'xuu6','张飞6','2007-03-29 12:54:41',6.5,'description6'),
(7,'xuu7','张飞7','2007-03-29 12:54:41',7.5,'description7'),
(8,'xuu8','张飞8','2007-03-29 12:54:41',8.5,'description8'),
(9,'xuu9','张飞9','2007-03-29 12:54:41',9.5,'description9'),
(10,'xuu10','张飞10','2007-03-29 12:54:41',10.5,'description10'),
(11,'xuu11','张飞11','2007-03-29 12:54:41',11.5,'description11'),
(12,'xuu12','张飞12','2007-03-29 12:54:41',12.5,'description12'),
(13,'xuu13','张飞13','2007-03-29 12:54:41',13.5,'description13'),
(14,'xuu14','张飞14','2007-03-29 12:54:41',14.5,'description14'),
(15,'xuu15','张飞15','2007-03-29 12:54:41',15.5,'description15'),
(16,'xuu16','张飞16','2007-03-29 12:54:41',16.5,'description16'),
(17,'xuu17','张飞17','2007-03-29 12:54:41',17.5,'description17'),
(18,'xuu18','张飞18','2007-03-29 12:54:41',18.5,'description18'),
(19,'xuu19','张飞19','2007-03-29 12:54:41',19.5,'description19'),
(20,'xuu20','张飞20','2007-03-29 12:54:41',20.5,'description20'),
(21,'xuu21','张飞21','2007-03-29 12:54:41',21.5,'description21'),
(22,'xuu22','张飞22','2007-03-29 12:54:41',22.5,'description22'),
(23,'xuu23','张飞23','2007-03-29 12:54:41',23.5,'description23'),
(24,'xuu24','张飞24','2007-03-29 12:54:41',24.5,'description24'),
(25,'xuu25','张飞25','2007-03-29 12:54:41',25.5,'description25'),
(26,'xuu26','张飞26','2007-03-29 12:54:41',26.5,'description26'),
(27,'xuu27','张飞27','2007-03-29 12:54:41',27.5,'description27'),
(28,'xuu28','张飞28','2007-03-29 12:54:41',28.5,'description28'),
(29,'xuu29','张飞29','2007-03-29 12:54:41',29.5,'description29'),
(30,'xuu30','张飞30','2007-03-29 12:54:41',30.5,'description30'),
(31,'xuu31','张飞31','2007-03-29 12:54:41',31.5,'description31'),
(32,'xuu32','张飞32','2007-03-29 12:54:41',32.5,'description32'),
(33,'xuu33','张飞33','2007-03-29 12:54:41',33.5,'description33'),
(34,'xuu34','张飞34','2007-03-29 12:54:41',34.5,'description34'),
(35,'xuu35','张飞35','2007-03-29 12:54:41',35.5,'description35'),
(36,'xuu36','张飞36','2007-03-29 12:54:41',36.5,'description36'),
(37,'xuu37','张飞37','2007-03-29 12:54:41',37.5,'description37'),
(38,'xuu38','张飞38','2007-03-29 12:54:41',38.5,'description38'),
(39,'xuu39','张飞39','2007-03-29 12:54:41',39.5,'description39'),
(40,'xuu40','张飞40','2007-03-29 12:54:41',40.5,'description40'),
(41,'xuu41','张飞41','2007-03-29 12:54:41',41.5,'description41'),
(42,'xuu42','张飞42','2007-03-29 12:54:41',42.5,'description42'),
(43,'xuu43','张飞43','2007-03-29 12:54:41',43.5,'description43'),
(44,'xuu44','张飞44','2007-03-29 12:54:41',44.5,'description44'),
(45,'xuu45','张飞45','2007-03-29 12:54:41',45.5,'description45'),
(46,'xuu46','张飞46','2007-03-29 12:54:41',46.5,'description46'),
(47,'xuu47','张飞47','2007-03-29 12:54:41',47.5,'description47'),
(48,'xuu48','张飞48','2007-03-29 12:54:41',48.5,'description48'),
(49,'xuu49','张飞49','2007-03-29 12:54:41',49.5,'description49'),
(50,'xuu50','张飞50','2007-03-29 12:54:41',50.5,'description50');


存储过程调用测试:

-- 方法原型 `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)




如果你对改存储过程有什么改进和优化的地方，欢迎指教！

[hy2003fly]

顶，支持!

[alexlee002]

觉得很麻烦，
其实在mysql里边要实现分页， 用不到存储过程。
用limit就可以，有点类似mssql的top， 不过比top方便

select * from table_name where where_condition limit offset, number;

[xuu27]

这其实用到limit了啊;
我的存储过程了只不过包装了:参数来设定查找条件和参数化limit的offset, number;

由于limit的参数不能动态的，所以得用动态sql的EXECUTE 方法来处理而已。

[whalefish2001]

Mark

[懒得去死]

帮顶一下吧.

[httpnet]

在mysql中存储过程的效率还不如直接查询。

[jerrycool]

是不是可以作以下改进!
1.动态指定表...你这样不是要一张表一个存储过程?
2.from可以为sql查询语句!

第二点我也在做,不过找不到好的方案.
aspx里,我用邹大哥的存储过程.

[Guizhi]

drop procedure if exists _PageQuery;


我的, 够简单吧,呵呵

DELIMITER $$
CREATE PROCEDURE _PageQuery (
IN queryStr varchar(8000), -- 查询语句
IN beginRow int, -- 页起始的位置
IN pageSize int -- 每页行数
)
BEGIN

set @sql = concat(queryStr, ' limit ?, ?');
set @row = beginRow;
set @size = pageSize;
PREPARE myStmt FROM @sql;
EXECUTE myStmt USING @row, @size;
DEALLOCATE PREPARE myStmt;

END $$
DELIMITER ;

[liguangyan]

厉害，学习了

[xzhcjp]

SET @sql = 'SELECT userid FROM mysqltestuser ';
这里局限了只能从mysqltestuser表中分页，其他表就不行了

SELECT COUNT(*) AS RecordCount FROM _TempTable_KeyID;
SET @startPoint = ifnull((_PageIndex-1)*_PageSize,0);
这里错了吧！RecordCount都没有用到

[xzhcjp]

第二个是我看错了。