大家什么好的分页存储过程吗?我写了一个,但是觉得很臃肿,不好用,请大家贴出自己的分页吧. 如题:请大家帮帮>>呵呵>> 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 转一个给你参考一下,原贴查阅http://topic.csdn.net/u/20070330/11/f062cd75-8a49-4ec6-9401-2709f607c1bb.html存储过程分页的基本原理:我们先对查找到的记录集(支持输入查找条件_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) http://blog.csdn.net/yueliangdao0608/archive/2008/10/23/3130442.aspx 如何优化以下SQL语句 求教简单sql查询动态表名 mysql do while 问题 用C语言把数据存入数据库 送分来了,MySQL用PhpMyAdmin导入问题 这句语句有什么问题吗? ms sql数据导入MYSQL Mysql中如何比较两个日期的大小? 一个删除语句如何保留固定的行数 如何把同一个表中的两条记录合并 如何优化下面的sql 求解mysql数据库本身乱码问题
_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)