近一段时间使用mysql数据库做项目,用到了分页存储过程,从网上找了一个,当时的sql语句中有一些注释,试验了半天也没有成功,报缺参数等错误,最后将
存储过程中的空格都用一个空格替换,将注释去掉就可以了,可能是mysql存储过程对注释有特殊限制。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
mysql 分页存储过程
DELIMITER $$
USE `rhjt-teamwork`$$
DROP PROCEDURE IF EXISTS `p_test`$$
CREATE DEFINER=`root`@`%` PROCEDURE `p_test`(
IN p_table_name VARCHAR(100) ,
IN p_order_string VARCHAR(100),
IN p_page_size INT ,
IN p_page_now INT ,
IN p_where_string VARCHAR(100),
IN p_fields VARCHAR(100),
OUT p_out_rows INT
)
COMMENT '分页存储过程'
BEGIN
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string VARCHAR(128);
/*构造语句*/
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_out_rows = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END$$
DELIMITER ;