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                --输出的总页数