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

Question

总是习惯了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)
如果你对改存储过程有什么改进和优化的地方，欢迎指教！

Answer 1

觉得很麻烦， 
其实在mysql里边要实现分页， 用不到存储过程。
用limit就可以，有点类似mssql的top， 不过比top方便select * from table_name where where_condition limit offset, number;

Answer 2

这其实用到limit了啊;
我的存储过程了只不过包装了:参数来设定查找条件和参数化limit的offset, number;由于limit的参数不能动态的，所以得用动态sql的EXECUTE 方法来处理而已。

Answer 3

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

Answer 4

是不是可以作以下改进!
1.动态指定表...你这样不是要一张表一个存储过程?
2.from可以为sql查询语句!第二点我也在做,不过找不到好的方案.
aspx里,我用邹大哥的存储过程.

Answer 5

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 ;

Answer 6

   SET   @sql   =   'SELECT     userid   FROM   mysqltestuser '; 
这里局限了只能从mysqltestuser表中分页，其他表就不行了SELECT   COUNT(*)   AS   RecordCount   FROM   _TempTable_KeyID; 
  SET   @startPoint   =   ifnull((_PageIndex-1)*_PageSize,0); 
这里错了吧！RecordCount都没有用到