在业务系统里，列表查询几乎都要分页。如果每次都在应用代码里写limit和offset，遇到深翻页时MySQL依然要扫描前面大量记录。把分页封装成存储过程，可以让数据库一次性返回当前页数据和总条数，减少应用与数据库之间的交互次数，也统一了分页写法。
为什么用存储过程做分页
应用层分页通常分两步：先查count得到总数，再查limit得到数据。两次请求各自建立执行计划，若SQL复杂，优化器重复计算代价较高。存储过程在一个会话中顺序执行多条语句，可以把计数和取数放在同一个过程里，利用局部变量传递条件，避免重复解析。
另外，存储过程支持参数化调用，前端只需传页码和每页大小，后端不必拼接不同分页SQL。对于多表关联、带过滤条件的列表，统一在过程内维护where条件模板，降低出错概率。不过MySQL的存储过程调试不如外部语言方便，逻辑应尽量简单清晰。
基础分页存储过程示例
下面过程接收表名、页码、页大小，返回指定页数据与总记录数。这里用动态SQL是因为表名不能直接作为参数绑定，需用prepare执行。注意对页码做最小值保护，防止负数或零导致计算错误。
delimiter //
create procedure sp_paginate(
in p_table varchar(64),
in p_page int,
in p_size int,
out p_total int
)
begin
declare v_start int default 0;
declare v_sql varchar(1000);
declare v_count_sql varchar(1000);
if p_page < 1 then
set p_page = 1;
end if;
if p_size < 1 then
set p_size = 10;
end if;
set v_start = (p_page - 1) * p_size;
set v_count_sql = concat('select count(*) into @cnt from ', p_table);
set @csql = v_count_sql;
prepare stmt_count from @csql;
execute stmt_count;
deallocate prepare stmt_count;
set p_total = @cnt;
set v_sql = concat('select * from ', p_table, ' limit ', v_start, ',', p_size);
set @dsql = v_sql;
prepare stmt_data from @dsql;
execute stmt_data;
deallocate prepare stmt_data;
end //
delimiter ;
调用时可以用call sp_paginate('user', 2, 20, @total);然后select @total;查看总数。过程内用declare定义局部变量，用concat拼装字符串，再借助prepare和execute运行动态语句。这种写法适合表名固定的内部系统，若表名来自用户输入需严格白名单校验，避免SQL注入。
上面的例子没有过滤条件，实际业务中往往要加where。可以把条件作为额外入参，或者在过程内用if判断拼装。由于MySQL动态SQL不能绑定like的变量名到limit，只能在拼串阶段处理，因此所有外部值都要用quote过滤或提前参数化。
带条件的分页存储过程
假设用户表有status字段，需要按状态筛选并分页。我们增加入参p_status，并在计数与查询中拼接相同条件，保证总数与数据一致。
delimiter //
create procedure sp_paginate_user(
in p_page int,
in p_size int,
in p_status tinyint,
out p_total int
)
begin
declare v_start int default 0;
declare v_where varchar(200);
if p_page < 1 then set p_page = 1; end if;
if p_size < 1 then set p_size = 10; end if;
set v_start = (p_page - 1) * p_size;
set v_where = concat(' where status = ', p_status);
set @csql = concat('select count(*) into @cnt from user', v_where);
prepare s1 from @csql;
execute s1;
deallocate prepare s1;
set p_total = @cnt;
set @dsql = concat('select id,name from user', v_where, ' order by id desc limit ', v_start, ',', p_size);
prepare s2 from @dsql;
execute s2;
deallocate prepare s2;
end //
delimiter ;
这里把where条件存到变量v_where，计数和取数复用，避免两边写法不一致导致页码错乱。order by放在取数语句中，计数不需要排序，减少临时表开销。如果status字段有索引，where条件能快速缩小范围，limit的深分页问题会明显缓解。
需要提醒的是，MySQL的prepare语句作用域是会话级，用完后deallocate释放，防止会话中堆积过多预处理句柄。存储过程结束时局部变量自动销毁，但用户变量如@cnt仍在会话里，调用方读取后建议置空。
性能与避坑要点
深分页本身无法靠存储过程消除，因为limit offset还是要跳过前N行。若offset极大，即使过程内执行也会慢。此时可改用基于主键游标的分页，比如where id < 上一页最小id limit size，把偏移转为范围扫描。存储过程同样能封装这种逻辑，只需把入参从页码改为last_id。
| 方案 | 优点 | 缺点 |
|---|---|---|
| 页码+limit offset | 调用简单，支持任意跳页 | 深翻页慢，扫描冗余行 |
| 游标主键分页 | 性能稳定，不随页数下降 | 不支持直接跳到指定页 |
| 应用层分页 | 逻辑灵活，易调试 | 网络往返多，计数重复 |
在存储过程里还应校验p_size上限，防止一次性拉取过多行撑爆内存。可以设最大每页500，超出的按500处理。此外，过程内尽量避免使用游标逐行fetch，因为MySQL游标要把结果集物化到临时表，不如直接select limit高效。
最后，存储过程的分页权限要单独授予，避免业务账号拥有create routine后又被注入篡改。定期用explain模拟过程内SQL，确认走了预期索引。只要条件索引合理、偏移可控，存储过程分页能在简化代码的同时保持较好性能。