Mysql分页存储过程

CREATE PROCEDURE `prc_query`(
        IN p_table_name VARCHAR(2000),
        IN p_fields VARCHAR(3000),
        IN p_where_string VARCHAR(8000),
        IN p_group_string VARCHAR(3000),
        IN p_order_string VARCHAR(3000),
        IN p_page_now INTEGER,
        IN p_page_size INTEGER,
        OUT p_total_records INTEGER,
        OUT p_total_pages INTEGER
    )
    NOT DETERMINISTIC
    CONTAINS SQL
    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);
   
    #如果页索引和页大小都为0，则返回所有数据
    IF p_page_now = 0 THEN
     SET m_limit_string = '';
    END IF;
   
    IF ISNULL(p_where_string) THEN
     SET p_where_string = '';
    END IF;
   
    IF ISNULL(p_group_string) THEN
     SET p_group_string = '';
    END IF;
    IF LENGTH(TRIM(p_group_string)) > 0 THEN
     SET p_group_string = CONCAT(' group by ',p_group_string);
    END IF;
   
    SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' where 1=1 ', p_where_string,' ',p_group_string);
    SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' where 1=1 ', p_where_string, ' ',p_group_string, ' order by ', p_order_string, m_limit_string);

    #预处理
    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt; 
   
    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
   
    #计算机总记录数和总页数
    SET p_total_records = @ROWS_TOTAL;
    IF p_page_size <> 0 THEN
   IF p_total_records MOD p_page_size = 0 THEN
         SET p_total_pages = p_total_records / p_page_size;
     ELSE
         SET p_total_pages = floor(p_total_records / p_page_size) + 1;
     END IF;
    ELSE
     SET p_total_pages = 1;
    END IF;
   
END;