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_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 )