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

总是习惯了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)

 

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