存储过程动态分页的实现原理
在MySQL中，原生的LIMIT子句不支持直接绑定参数，因此无法直接在存储过程中通过传入的偏移量和行数变量直接使用LIMIT。要实现动态传入LIMIT的参数，需要借助动态SQL的方式，先拼接包含LIMIT子句的完整SQL语句，再通过预处理执行该语句，最终返回分页后的结果。
基础存储过程创建示例
以下是一个实现动态分页的存储过程示例，支持传入表名、查询条件、偏移量、每页行数四个参数，返回分页后的数据以及总条数：
-- 创建存储过程，实现动态分页
DELIMITER //
CREATE PROCEDURE sp_dynamic_page(
IN p_table_name VARCHAR(100), -- 查询的表名
IN p_where_condition VARCHAR(500), -- 查询条件，可传空字符串
IN p_offset INT, -- 偏移量，从0开始
IN p_page_size INT -- 每页行数
)
BEGIN
-- 定义变量存储动态SQL和总条数
DECLARE v_sql VARCHAR(2000);
DECLARE v_count_sql VARCHAR(2000);
DECLARE v_total INT;
-- 校验参数合法性，避免无效查询
IF p_offset < 0 THEN
SET p_offset = 0;
END IF;
IF p_page_size <= 0 THEN
SET p_page_size = 10;
END IF;
-- 拼接查询总条数的SQL
SET v_count_sql = CONCAT('SELECT COUNT(*) INTO @total FROM ', p_table_name);
IF p_where_condition != '' THEN
SET v_count_sql = CONCAT(v_count_sql, ' WHERE ', p_where_condition);
END IF;
-- 执行总条数查询
SET @count_stmt = v_count_sql;
PREPARE count_stmt FROM @count_stmt;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET v_total = @total;
-- 拼接分页查询的动态SQL
SET v_sql = CONCAT('SELECT * FROM ', p_table_name);
IF p_where_condition != '' THEN
SET v_sql = CONCAT(v_sql, ' WHERE ', p_where_condition);
END IF;
SET v_sql = CONCAT(v_sql, ' LIMIT ', p_offset, ', ', p_page_size);
-- 执行分页查询
SET @page_stmt = v_sql;
PREPARE page_stmt FROM @page_stmt;
EXECUTE page_stmt;
DEALLOCATE PREPARE page_stmt;
-- 返回总条数
SELECT v_total AS total_count;
END //
DELIMITER ;
存储过程调用示例
假设我们要查询user_info表，筛选status = 1的用户，从第0条开始，每页查询5条数据，调用方式如下：
-- 调用存储过程
CALL sp_dynamic_page('user_info', 'status = 1', 0, 5);
执行后会返回两个结果集，第一个是分页后的用户数据，第二个是符合条件的总条数。
注意事项
- 由于使用了动态SQL拼接表名和条件，需要严格校验传入的参数，避免SQL注入风险，生产环境中不建议直接传入未校验的表名和查询条件。
- LIMIT的偏移量过大会导致查询性能下降，建议结合业务场景限制最大偏移量。
- 如果查询条件中包含特殊字符，需要在传入前做好转义处理，避免SQL语法错误。
- 不同数据库的存储过程语法存在差异，上述示例仅适用于MySQL，其他数据库需要调整动态SQL的实现方式。