mysql毫秒级百万数据分页存储过程_mssql千万级分页存储过程分享

最新推荐文章于 2021-02-08 03:12:17 发布
最新推荐文章于 2021-02-08 03:12:17 发布
阅读量159 收藏
点赞数
文章标签： mysql毫秒级百万数据分页存储过程
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39633102/article/details/113460377
版权

Create PROC P_viewPage

/**//*

nzperfect [no_mIss] 高效通用存储过程(双向检索) 2007.5.7 QQ:34813284

敬告：适用于单一主键或存在唯一值列的表或视图

:Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围

*/

@TableName VARCHAR(200), --表名

@FieldList VARCHAR(2000), --显示列名，如果是全部字段则为*

@PrimaryKey VARCHAR(100), --单一主键或唯一值键

@Where VARCHAR(2000), --查询条件 不含'where'字符，如id>10 and len(userid)>9

@Order VARCHAR(1000), --排序 不含'order by'字符，如id asc,userid desc，必须指定asc或desc

--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷

@SortType INT, --排序规则 1:正序asc 2:倒序desc 3:多列排序方法

@RecorderCount INT, --记录总数 0:会返回总记录

@PageSize INT, --每页输出的记录数

@PageIndex INT, --当前页数

@TotalCount INT OUTPUT , --记返回总记录

@TotalPageCount INT OUTPUT --返回总页数

AS

SET NOCOUNT ON

IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0

SET @Order = RTRIM(LTRIM(@Order))

SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))

SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')

WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0

BEGIN

SET @Order = REPLACE(@Order,', ',',')

SET @Order = REPLACE(@Order,' ,',',')

END

IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''

or ISNULL(@PrimaryKey,'') = ''

or @SortType < 1 or @SortType >3

or @RecorderCount < 0 or @PageSize < 0 or @PageIndex < 0

BEGIN

PRINT('ERR_00')

RETURN

END

IF @SortType = 3

BEGIN

IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')

BEGIN PRINT('ERR_02') RETURN END

END

DECLARE @new_where1 VARCHAR(1000)

DECLARE @new_where2 VARCHAR(1000)

DECLARE @new_order1 VARCHAR(1000)

DECLARE @new_order2 VARCHAR(1000)

DECLARE @new_order3 VARCHAR(1000)

DECLARE @Sql VARCHAR(8000)

DECLARE @SqlCount NVARCHAR(4000)

IF ISNULL(@where,'') = ''

BEGIN

SET @new_where1 = ' '

SET @new_where2 = ' Where '

END

ELSE

BEGIN

SET @new_where1 = ' Where ' + @where

SET @new_where2 = ' Where ' + @where + ' AND '

END

IF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2

BEGIN

IF @SortType = 1

BEGIN

SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'

SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'

END

IF @SortType = 2

BEGIN

SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'

SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'

END

END

ELSE

BEGIN

SET @new_order1 = ' orDER BY ' + @Order

END

IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0

BEGIN

SET @new_order1 = ' orDER BY ' + @Order

SET @new_order2 = @Order + ','

SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')

SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')

SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)

IF @FieldList <> '*'

BEGIN

SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')

SET @FieldList = ',' + @FieldList

WHILE CHARINDEX(',',@new_order3)>0

BEGIN

IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0

BEGIN

SET @FieldList =

@FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))

END

SET @new_order3 =

SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))

END

SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))

END

END

SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'

+ CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'

IF @RecorderCount = 0

BEGIN

EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',

@TotalCount OUTPUT,@TotalPageCount OUTPUT

END

ELSE

BEGIN

Select @TotalCount = @RecorderCount

END

IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)

BEGIN

SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)

END

IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)

BEGIN

IF @PageIndex = 1 --返回第一页数据

BEGIN

SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1

END

IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))

+ ' ' + @FieldList + ' FROM '

+ @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1

END

END

ELSE

BEGIN

IF @SortType = 1 --仅主键正序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '

+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'Select TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '

+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

END

IF @SortType = 2 --仅主键反序排序

BEGIN

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' < '

+ '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '

+ STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey

+' FROM '+ @TableName

+ @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('

+ 'Select TOP ' + STR(@PageSize) + ' '

+ @FieldList + ' FROM '

+ @TableName + @new_where2 + @PrimaryKey + ' > '

+ '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '

+ STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey

+ ' FROM ' + @TableName

+ @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2

+ ' ) AS TMP ' + @new_order1

END

END

IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理

BEGIN

IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0

BEGIN PRINT('ERR_02') RETURN END

IF @PageIndex <= CEILING((@TotalCount+0.0)/@PageSize)/2 --正向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '

+ @new_order2 + ' ) AS TMP ' + @new_order1

END

ELSE --反向检索

BEGIN

SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '

+ ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList

+ ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '

+ @new_order1 + ' ) AS TMP ' + @new_order1

END

END

END

PRINT(@SQL)

EXEC(@Sql)

weixin_39633102
关注
分页优化--pageHelper自定义分页--分析分页插件源码
mucheng_的博客
12-31 765
经过刚刚SQL的优化，那我们想返回具体的首页、末页，分页数，就需要用到分页插件了。/*** @Description: 自定义pageHelper的limit分页* @Date: 2022/12/21 下午6:10*///正则表达式/*** 把limit语句放到 MAPPINGLIMIT标记所在的位置，也就是主表的位置，对主表进行分页** @return 加limit后的sql*/@Override//如果不匹配正则，走原始的sqlif (!}//标记前的sql语句。
Mysql部署全攻略——等保安全配置
最新发布
bbsxb520的博客
06-16 585
修改默认超管用户、检查空密码用户、配置超时及错误次数、密码复杂度设置、账号过期设置、开启全量日志、开启加密链接（包括秘钥的生成和使用、强制用户使用安全链接的方法、主从同步配置等）、审计日志功能如何实现（使用Mcafee插件）
mysql下的千万级分页存储过程
09-17
改造过的mysql版本的分页存储过程，严格测试，绝对好用．适合大型项目使用．
千万数量级分页存储过程
Danisの一棵树的软件历程
01-14 637
/* *************************************************************** ** 中国无忧商务网千万数量级分页存储过程 ** *************************************************************** 参数说明: 1.Tables :表名称,视图 2.PrimaryKey :主关键字
实现千万级数据的分页通用存储过程
weixin_33868027的博客
03-09 114
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go /* 功能描述： 通用分页显示查询 如果有自增标识字段，在@strGetFields中不要加入此字段信息， 如果非要加入的话，要 (fldName + 0) AS fldName 这样处理； 输入参数： @tblName: 表名 @strGetFields: 需要返回的列 ...
实现千万级数据分页的存储过程
01-30 218
经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：exec GetRecordFromPage news,newsid,10,100000news 为表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。/*  函数名称: GetRecord
高性能mysql分页存储过程
10-08
非常好用的mysql存储过程，支持大并发，很稳定，绝对原创，你值得拥有！
叶子分页类V0.12版 asp
06-16
- `sp_Util_Page.sql`：这个文件可能是包含存储过程的SQL脚本，用于配合分页类进行数据库操作，例如获取总记录数或进行分页查询。 - `ReadMe.txt`：通常包含项目的基本介绍、使用说明或注意事项，是理解和使用此...
【若泽大数据】MySQL参数详解
强悍de巨人
04-23 1744
系统变量提供的是各种与服务器配置和功能有关的信息。大部分的系统变量都可以在服务器启动时进行设置。在运行时，每一个系统变量都拥有一个全局值或会话值，或者同时拥有这两个值。许多系统变量都是动态的，也就是说，可以在服务器郑州运行时修改它们的值。这些类型的信息都会在每个变量的描述里（与变量名同处一行）指明。对于那些可以在服务器启动时设置的变量，看到的是“自动”一词，后面紧跟着“直接设置”或某个选项。“直接...
阿里数据库内核月报导航
luoxn28的专栏
09-27 1382
数据库内核月报是阿里云RDS-数据库内核组维护的一个关于数据库的技术博客，上面的文章质量是有保证的。不过有一点不太友好的是，这个网站首页非常简洁，只是按照时间月份来归档文章，并没有按照...
MySQL 百万级分页优化(Mysql千万级快速分页)
12-15
以下分享一点我的经验 一般刚开始学SQL的时候，会这样写 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候，这样写会慢死 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10; 是的，速度提升到0.x秒了，看样子还行了 可是，
mysql高效分页存储过程_mysql 存储过程高效分页方法
weixin_30744725的博客
01-19 244
我们提供这种mysql查询分页是一款要以针对上亿条大容易的数据进行分页并且是速度很快的，下面我们就来看看mysql存储过程的详细写法吧。Create procedure [dbo].[PrGs_Nation_Task_GetList]@PageSize int = 100, -- 每页显示记录条数，默认为100@PageIndex int = 1, -- 当前提取要显示的页码，默认为1，数据库根据...
MS SqlSever一千万条以上记录分页数据库优化经验总结【索引优化 + 代码优化】[转]...
baihuomeng2210的博客
01-04 198
MS SqlSever一千万条以上记录分页数据库优化经验总结【索引优化 + 代码优化】[转] 对普通开发人员来说经常能接触到上千万条数据优化的机会也不是很多，这里还是要感谢公司提供了这样的一个环境，而且公司让我来做优化工作。当数据库中的记录不超过10万条时，很难分辨...
mysql毫秒级百万数据分页存储过程_毫秒级百万数据分页存储过程[欢迎转载]
weixin_29378975的博客
01-20 158
数据分页一直是一个经久不衰的话题,在园子里看到了N多高手,都比我厉害,今天把我使用的分页代码继续分享;请高手手下留情,少拍砖啦...哈哈;欢迎大家转载!!!!首先参考园子里的高手方法生成一个百万数据表; 耗时 33 秒;然后创建分页存储过程;这个是基于SQL 2005 的ROW_NUMBER的, SQL 2000 不适用; SQL 2008 我没有安装,所以也没有测试过!估计应该可以运行.2种算法...
c mysql分页存储过程_精彩的近乎完美的分页存储过程_MySQL
weixin_35610373的博客
01-26 110
CREATE procedure main_table_pwqzc(@pagesize int,@pageindex int,@docount bit,@this_id)asif(@docount=1)beginselect count(id) from luntan where this_id=@this_idendelsebegindeclare @indextable table(id in...
Mysql分页查询通用存储过程
热门推荐
阿影的黄金时代
03-07 1万+
前段时间没有给出SQLServer转到Mysql的通用存储过程，本着共享的精神，为大家奉献这段Mysql分页查询通用存储过程，假设所用数据库为guestbook： use guestbook;delimiter $$drop procedure if exists prc_page_result $$create procedure prc_page_result (in currpage
mysql分页存储_MySql分页存储过程
weixin_35355756的博客
02-08 387
分页获取数据 codeDELIMITER $$DROP PROCEDURE IF EXISTS `testDB`.`GetRecordAsPage` $$CREATE PROCEDURE `testDB`.`GetRecordAsPage` (in tbName varchar(800),in fldName varchar(1000),in strWhere varchar(500),in pa...
MySQL动态分页存储过程实现
"该资源提供了一个MySQL的分页存储过程，允许用户动态输入表名、查询条件、排序列以及分页参数，适用于多种分页查询场景。" 在MySQL数据库中，存储过程是一种预编译的SQL语句集合，可以在需要时重复调用，提高效率...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值