MySQL存储过程分页如何返回记录总数

Question

这是一段从网上搜到的代码,但是会报oRowsTotal没有定义的错误Error Code : 1193
Unknown system variable 'oRowsTotal'DROP PROCEDURE IF EXISTS `PtBase`.`prPager`;
CREATE PROCEDURE `PtBase`.`prPager`(
    OUT oRowsTotal INT,        --输出记录总数
    IN iTableName VARCHAR(800),    --表名
    IN iFields VARCHAR(800),    --查询字段
    IN iPageSize INT,        --每页记录数
    IN iPageNow INT,        --当前页
    IN iOrderString VARCHAR(100),    --排序条件
    IN iWhereString VARCHAR(800)    --WHERE条件
)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE _beginRow INT DEFAULT 0;
    DECLARE _mainString VARCHAR(8000);
    DECLARE _limitString VARCHAR(100);
    DECLARE _countString VARCHAR(8000);
    
    SET _beginRow = (iPageNow - 1) * iPageSize;
    SET _limitString = CONCAT(' LIMIT ', beginRow, ', ', iPageSize);
    SET _countString = CONCAT('SET oRowsTotal = SELECT COUNT(*) FROM ', iTableName, ' WHERE ', iWhereString);
    SET _mainString = CONCAT('SELECT ', iFields, ' FROM ', iTableName, ' WHERE ', iWhereString, iOrderString, _limitString);    SET @countStr = _countString;
    PREPARE countStmt FROM @countStr;
    EXECUTE countStmt;
    DEALLOCATE PREPARE countStmt;    SET @mainStr = _mainString;
    PREPARE mainStmt FROM @mainStr;
    EXECUTE mainStmt;
    DEALLOCATE PREPARE mainStmt;
END;

Answer 1

试下 limit 关键字
limit 0，100 就是取出前100条数据
至于总数可以先count（）一下。

Answer 2

请仔细看上面的代码，及我所问的问题，简单说来就是如何从动态SQL中赋值给当前的输出变量。

Answer 3

改成如下试试，MySQL  一般是 select column into var from tbl    -- SET _countString = CONCAT('SET oRowsTotal = SELECT COUNT(*) FROM ', iTableName, ' WHERE ', iWhereString);
    SET _countString = CONCAT('SELECT COUNT(*) into oRowsTotal FROM ', iTableName, ' WHERE ', iWhereString);


Answer 4

看了一个你的程序中想实现的功能，做了两次几乎相同的查询，第一次仅是为了取得 符合条件的记录数，其实不必这样。可以直接在你的SELECT语句中加上修饰SQL_CALC_FOUND_ROWS加上SQL_CALC_FOUND_ROWS之后，即使你用了limit n,m ，SELECT FOUND_ROWS() 仍会返回满足条件记录的总数。这样，你执行完 select SQL_CALC_FOUND_ROWS  之后，再取一下记录总数就行了。 参考一下下面这个贴子中 31楼  的例子。http://topic.csdn.net/u/20090522/09/bb244d6e-528f-45f4-8829-dfa955c5ba82.html?92159

Answer 5

改成INTO也一样报错Error Code : 1327
Undeclared variable: oRowsTotal

Answer 6

是的，因为你的这句是用EXECUTE countStmt;来执行的，已经不是在你的存储过程代码中了，当mysql执行这个的时候，在系统中并没有这个变量。SET _countString = CONCAT('SELECT COUNT(*) into @oRowsTotal FROM ', iTableName, ' WHERE ', iWhereString);
...
EXECUTE countStmt;SET oRowsTotal  = @oRowsTotal ;

Answer 7

按照 http://topic.csdn.net/u/20090522/09/bb244d6e-528f-45f4-8829-dfa955c5ba82.html?92159 改成如下。BEGIN
    DECLARE _beginRow INT DEFAULT 0;
    DECLARE _mainString VARCHAR(8000);
    DECLARE _limitString VARCHAR(100);
    DECLARE _countString VARCHAR(8000);
    
    SET _beginRow = (iPageNow - 1) * iPageSize;
    SET _limitString = CONCAT(' LIMIT ', beginRow, ', ', iPageSize);
    SET _mainString = CONCAT('SELECT SQL_CALC_FOUND_ROWS ', iFields, ' FROM ', iTableName, ' WHERE ', iWhereString, iOrderString, _limitString);    SET @mainStr = _mainString;
    PREPARE mainStmt FROM @mainStr;
    EXECUTE mainStmt;
    DEALLOCATE PREPARE mainStmt;
    set oRowsTotal = FOUND_ROWS() ;
    
END;