mysql毫秒级百万数据分页存储过程_毫秒级百万数据分页存储过程

/****** Object: StoredProcedure [dbo].[up_Page2005] Script Date: 11/28/2013 17:10:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[up_Page2005]

(

@TableName VARCHAR(MAX) , --表名

@Fields VARCHAR(5000) = ''*'' , --字段名(全部字段为*)

@OrderField VARCHAR(5000) , --排序字段(必须!支持多字段)

@sqlWhere VARCHAR(5000) = NULL ,--条件语句(不用加where)

@pageSize INT , --每页多少条记录

@pageIndex INT = 1 --指定当前为第几页

)

AS

BEGIN

DECLARE @sql NVARCHAR(MAX);

DECLARE @totalRecord INT;

DECLARE @TotalPage INT;

--计算总记录数

IF ( @SqlWhere = ''''

OR @sqlWhere = NULL

)

SET @sql = ''select @totalRecord = count(*) from '' + @TableName

ELSE

SET @sql = ''select @totalRecord = count(*) from '' + @TableName

+ '' where 1=1 '' + @sqlWhere

EXEC sp_executesql @sql, N''@totalRecord int OUTPUT'',

@totalRecord OUTPUT--计算总记录数

--计算总页数

SELECT @TotalPage = CEILING(( @totalRecord + 0.0 ) / @PageSize)

IF ( @SqlWhere = ''''

OR @sqlWhere = NULL

)

SET @sql = ''Select top ('' + CONVERT(VARCHAR, @pageSize) + '') ''

+ @Fields + '' FROM (select ROW_NUMBER() Over(order by ''

+ @OrderField + '') as 编号,'' + @Fields + '' from '' + @TableName

ELSE

SET @sql = ''Select top ('' + CONVERT(VARCHAR, @pageSize) + '') ''

+ @Fields + '' FROM (select ROW_NUMBER() Over(order by ''

+ @OrderField + '') as 编号,'' + @Fields + '' from '' + @TableName

+ '' where 1=1 '' + @SqlWhere

--处理页数超出范围情况

IF @PageIndex <= 0

SET @pageIndex = 1

IF @pageIndex > @TotalPage

SET @pageIndex = @TotalPage

--处理开始点和结束点

DECLARE @StartRecord INT

DECLARE @EndRecord INT

SET @StartRecord = ( @pageIndex - 1 ) * @PageSize + 1

SET @EndRecord = @StartRecord + @pageSize - 1

--继续合成sql语句

SET @Sql = @Sql + '') as t where 编号>='' + CONVERT(VARCHAR, @StartRecord)

+ '' order by 编号''

EXEC(@Sql)

IF @@Error <> 0

RETURN -1

ELSE

SELECT @totalRecord ,

@TotalPage ---返回记录总数,返回总页数

END