Mysql分页存储过程_MySQL

DROP PROCEDURE IF EXISTS pr_pager;CREATE PROCEDURE pr_pager(    IN    p_table_name        VARCHAR(1024),            IN    p_fields            VARCHAR(1024),            IN    p_page_size            INT,                    IN    p_page_now            INT,                    IN    p_order_string        VARCHAR(128),            IN    p_where_string        VARCHAR(1024),             OUT    p_page_count            INT                        )    NOT DETERMINISTIC     SQL SECURITY DEFINER     COMMENT '分页存储过程'    BEGIN        DECLARE m_begin_row INT DEFAULT 0;    DECLARE m_limit_string CHAR(64);            SET m_begin_row = (p_page_now - 1) * p_page_size;    SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);        SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string,m_limit_string);        PREPARE count_stmt FROM @COUNT_STRING;    EXECUTE count_stmt;    DEALLOCATE PREPARE count_stmt;    SET p_page_count = CEILING((@ROWS_TOTAL*1.0)/p_page_size);    PREPARE main_stmt FROM @MAIN_STRING;    EXECUTE main_stmt;    DEALLOCATE PREPARE main_stmt;    END;--测试call pr_pager("person","id",4,2,"order by id desc","",@page_count);select @page_count as page_count;

    IN    p_table_name         VARCHAR(1024),     --表名     IN    p_fields             VARCHAR(1024),     --

输出的字段名 IN p_page_size INT, --页容量 IN p_page_now INT, --当前页 IN p_order_string VARCHAR(128), --排序语句段 ，如：order by id desc IN p_where_string VARCHAR(1024), --where语句段 OUT page_count INT --输出的总页数