mysql 翻页 存储过程_Mysql分页存储过程

#定义变量

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);

#如果页索引和页大小都为0，则返回所有数据

IF p_page_now = 0 THEN

SET m_limit_string = '';

END IF;

IF ISNULL(p_where_string) THEN

SET p_where_string = '';

END IF;

IF ISNULL(p_group_string) THEN

SET p_group_string = '';

END IF;

IF LENGTH(TRIM(p_group_string)) > 0 THEN

SET p_group_string = CONCAT(' group by ',p_group_string);

END IF;

SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' where 1=1 ', p_where_string,' ',p_group_string);

SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' where 1=1 ', p_where_string, ' ',p_group_string, ' order by ', p_order_string, m_limit_string);

#预处理

PREPARE count_stmt FROM @COUNT_STRING;

EXECUTE count_stmt;

DEALLOCATE PREPARE count_stmt;

PREPARE main_stmt FROM @MAIN_STRING;

EXECUTE main_stmt;

DEALLOCATE PREPARE main_stmt;

#计算机总记录数和总页数

SET p_total_records = @ROWS_TOTAL;

IF p_page_size <> 0 THEN

IF p_total_records MOD p_page_size = 0 THEN

SET p_total_pages = p_total_records / p_page_size;

ELSE

SET p_total_pages = floor(p_total_records / p_page_size) + 1;

END IF;

ELSE

SET p_total_pages = 1;

END IF;

END;