22,301
社区成员
发帖
与我相关
我的任务
分享把MSSQL的存储过程转化成MySQL的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Common_GetPagingRecords]
@PageIndex int,
@PageSize int,
@FromClause varchar(512), --单个Table或者用Join关联的多个Table
@SelectFields varchar(4000) = '*', --查询字段，默认为 *
@WhereClause nvarchar(max) = N'', --条件例如"DirectoryID=4"
@SortField varchar(256), --排序字段
@SortFieldIsDesc bit = 1, --正序(0)还是倒序(1)
@ReturnRecordCount bit = 1, --是否需要返回查询到的记录数
@MaxRecords int = -1, --最多获取多少条记录
@TotalRecords int = -1 output, --查询到的记录数
@ReverseOrder bit = 0 output --1表示读取数据的时候 排序要反过来
AS
BEGIN
SET NOCOUNT ON;
if (@PageIndex < 0)
set @PageIndex = 0;
DECLARE @SQLString nvarchar(4000);
DECLARE @WhereString1 nvarchar(4000);
DECLARE @WhereString2 nvarchar(4000);
DECLARE @WhereString3 nvarchar(4000);
IF @WhereClause IS NULL OR @WhereClause = N'' BEGIN
SELECT @WhereString1 = N'';
SELECT @WhereString2 = N'WHERE ';
END
ELSE BEGIN
SELECT @WhereString1 = N'WHERE ' + @WhereClause;
SELECT @WhereString2 = N'WHERE ' + @WhereClause + N' AND ';
END
--1.处理查询记录数
IF @ReturnRecordCount=1
BEGIN
DECLARE @SQLCountString nvarchar(4000);
IF @MaxRecords>0
set @SQLCountString = N'select @TotalRecords = count(*) from (select top ' + str(@MaxRecords)+ N' '+ @SortField
+ N' from ' + @FromClause +N' ' + @WhereString1+ N') as TempCountTable';
ELSE
set @SQLCountString = N'select @TotalRecords = count(*) from ' + @FromClause + N' ' + @WhereString1;
exec sp_executesql @SQLCountString,N'@TotalRecords int out ',@TotalRecords out
if(@PageIndex*@PageSize > @TotalRecords)
set @PageIndex=0
END
else
set @TotalRecords = -1
IF (@MaxRecords>0 and @PageIndex*@PageSize>@MaxRecords)
set @PageIndex=0
IF @PageIndex = 0 BEGIN
SELECT @SQLString = N'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields + N' FROM ' + @FromClause + N' ' + @WhereString1 + ' ORDER BY ' + @SortField;
IF @SortFieldIsDesc = 1
SELECT @SQLString = @SQLString + ' DESC';
SET @ReverseOrder=0
END
ELSE BEGIN
-----------------------------------------------
DECLARE @SortField_NoPrefix varchar(256);
IF (CHARINDEX('.',@SortField)>0)
SET @SortField_NoPrefix = SUBSTRING(@SortField, CHARINDEX('.',@SortField)+1, LEN(@SortField));
ELSE
SET @SortField_NoPrefix = @SortField;
SET @SQLString='';
DECLARE @GetFromLast BIT
IF @TotalRecords=-1
SET @GetFromLast=0
ELSE BEGIN
DECLARE @TotalPage INT,@ResidualCount INT
SET @ResidualCount=@TotalRecords%@PageSize
IF @ResidualCount=0
SET @TotalPage=@TotalRecords/@PageSize
ELSE
SET @TotalPage=@TotalRecords/@PageSize+1
IF @PageIndex>@TotalPage/2 --页数过半，则从后往前算
SET @GetFromLast=1
ELSE
SET @GetFromLast=0
IF @GetFromLast=1 BEGIN
IF @PageIndex=@TotalPage-1 BEGIN
IF @ResidualCount=0
SET @ResidualCount=@PageSize;
SELECT @SQLString = N'SELECT top ' + STR(@ResidualCount)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause + N' ' + @WhereString1 + N' ORDER BY ' + @SortField;
IF @SortFieldIsDesc = 0--正序
SELECT @SQLString = @SQLString + ' DESC';
SET @ReverseOrder=1
END
ELSE IF @PageIndex>@TotalPage-1 BEGIN --已经超过最大页数
SELECT @SQLString = N'SELECT ' + @SelectFields
+ N' FROM ' + @FromClause + ' WHERE 0=1'
SET @ReverseOrder=0
END
ELSE BEGIN
SET @PageIndex=@TotalPage-(@PageIndex+1)
IF @SortFieldIsDesc=1
SET @SortFieldIsDesc=0
ELSE
SET @SortFieldIsDesc=1
SET @ReverseOrder=1
END
END
ELSE
SET @ReverseOrder=0
END
IF @SQLString='' BEGIN
DECLARE @TopCount INT
IF @GetFromLast=1 BEGIN
SET @TopCount=@PageSize * (@PageIndex-1)+@ResidualCount
IF @TopCount = 0
SET @TopCount = @PageSize;
END
ELSE
SET @TopCount=@PageSize * @PageIndex
IF @SortFieldIsDesc = 1
SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause+N' ' + @WhereString2 + @SortField + ' <=
(SELECT Min(' + @SortField_NoPrefix + ') FROM
(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @FromClause + N' ' + @WhereString1 + '
ORDER BY ' + @SortField + ' DESC) AS TempTable)
ORDER BY ' + @SortField + ' DESC';
ELSE
SELECT @SQLString = 'SELECT TOP ' + STR(@PageSize)
+ N' ' + @SelectFields
+ N' FROM ' + @FromClause + N' ' + @WhereString2 + @SortField + ' >=
(SELECT Max(' + @SortField_NoPrefix + ') FROM
(SELECT TOP ' + STR(@TopCount) + ' ' + @SortField + ' FROM ' + @FromClause+ N' ' + @WhereString1 + '
ORDER BY ' + @SortField + ' ASC) AS TempTable)
ORDER BY ' + @SortField;
END
END
EXEC sp_executesql @SQLString;
END
GO
这是一段MSSQL的分页存储过程,现在需要在MYSQL上面用,因为MSSQL不熟,不会翻译.大家帮忙一下!
...全文
请发表友善的回复…
发表回复
LongRui888 2013-11-12
- 打赏
- 举报
呵呵，给你找了一个，就是你的存储过程转化为Mysql的版本，你看看：
Mysql分页查询通用存储过程
http://www.knowsky.com/344381.html
--前段时间没有给出SQLServer转到MySQL的通用存储过程，本着共享的精神，
--为大家奉献这段Mysql分页查询通用存储过程，假设所用数据库为guestbook：
use guestbook;
delimiter $$
drop PRocedure if exists prc_page_result $$
create procedure prc_page_result (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
begin
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if;
if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');
end if;
else
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @ipageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
end;
$$
delimiter;
可以存储为数据库脚本，然后用命令导入：
mysql -u root -p < pageResult.sql;LongRui888 2013-11-12
- 打赏
- 举报
limit分页效率怎么样?这个存储过程是MSSQL上百万数据差不多也是毫秒级的,效率很高![/quote]
哦，那用mysql，估计够呛啊，Mysql只能运行一些，非常简单的语句，我建议你去网上找找Mysql的分页存储过程。
SX_xiaoxia 2013-11-12
- 打赏
- 举报
limit分页效率怎么样?这个存储过程是MSSQL上百万数据差不多也是毫秒级的,效率很高!
水族杰纶 2013-11-12
- 打赏
- 举报
体力活
mysql分页
用limit
