我必须在MySQL中使用LIMIT创建一个存储过程来获取分页数据。它在我的本地DB工作良好,但它不在服务器上工作。所以我试着在谷歌搜索并得到解决方案"升级服务器上的phpmyadmin版本",但这是不可能的。所以没有LIMIT或OFFSET的任何创建分页的替代方法。
?在我的代码下面我试过。
CREATE DEFINER=`root`@`localhost` PROCEDURE `LazyLoadScope`(IN ClientId INT,IN StartIndex INT,IN Count INT)
BEGIN
DECLARE LowerBound INT;
DECLARE UpperBound INT;
DECLARE rownum INT;
SET LowerBound = ((StartIndex - 1) * Count) + 1;
SET UpperBound = ((StartIndex - 1) * Count) + Count;
SELECT scopeid,scopename,clientid,scope,createddate,ViewDate,IsLocked
FROM scopemaster as sm inner join clientmaster cm on cm.clientid=sm.clientid
where cm.userid=ClientId order by sm.ViewDate desc LIMIT LowerBound,UpperBound ;
END
@kaushik请尝试下面的代码
CREATE DEFINER=`root`@`localhost` PROCEDURE `LazyLoadScope`(IN ClientId INT,IN StartIndex INT,IN Count INT)
BEGIN
DECLARE LowerBound INT;
DECLARE UpperBound INT;
DECLARE rownum INT;
SET LowerBound = ((StartIndex - 1) * Count) + 1;
SET UpperBound = ((StartIndex - 1) * Count) + Count;
SELECT scopeid,scopename,clientid,scope,createddate,ViewDate,IsLocked
from (SELECT *, @rownum := @rownum + 1 AS rank
from (SELECT sm.scopeid,sm.scopename,sm.clientid,sm.scope,sm.createddate,sm.ViewDate,sm.Is Locked
FROM scopemaster as sm
inner join clientmaster cm on cm.clientid=sm.clientid
where cm.userid=ClientId order by sm.ViewDate desc) d, (SELECT @rownum := 0) r ) m
WHERE rank >= LowerBound and rank <= UpperBound;
END