在mysql 存储过程实现分页, 排序
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_myuser_pager`(
`p_page` INT , -- 页号
`p_rows` INT , -- 每页行数
out total_row INT , -- 总共行数
`p_sort` VARCHAR(40) , -- 排序
`p_desc` VARCHAR(10) -- 升序降序
)
BEGIN
declare my_sqll varchar(500);
set p_page = p_page -1;
if (p_sort ='') then
set p_sort ='id';
end if;
select count(*) into total_row
from test_user ;
set my_sqll= CONCAT('SELECT *,',total_row,' total_row FROM test_user a JOIN ( select id from test_user
order by ',p_sort,' ' , `p_desc`, ' limit ',p_page * `p_rows`,', ', `p_rows`,' ) b on a.id = b.id order by a.',p_sort,' ' , `p_desc`);
set @ms=my_sqll;
PREPARE s1 from @ms;
EXECUTE s1;
deallocate prepare s1;
END