The actual operation process and code of the MySQL paging Stored Procedure

The following articles mainly describe the actual operation process of the MySQL paging stored procedure. We will introduce the actual application code to introduce the actual operation steps of the MySQL paging stored procedure, the following describes the main content of the article. I hope you will gain some benefits.

Drop procedure if exists pr_pager;

 
 

  
  
CREATE PROCEDURE pr_pager(  
  
  
IN p_table_name VARCHAR(1024),   
  
  
IN p_fields VARCHAR(1024),   
  
  
IN p_page_size INT,   
  
  
IN p_page_now INT,   
  
  
IN p_order_string VARCHAR(128),   
  
  
IN p_where_string VARCHAR(1024),   
  
  
OUT p_out_rows INT   
  
  
)  
  
  
NOT DETERMINISTIC  
  
  
SQL SECURITY DEFINER  
 
 

COMMENT 'paging stored Process'

BEGIN

Define Variables

 
 

  
  
DECLARE m_begin_row INT DEFAULT 0;  
  
  
DECLARE m_limit_string CHAR(64); 
 
 

MySQL construction statement in paging storage process

 
 

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

Preprocessing

 
 

  
  
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  
 
 

The above content is an introduction to the MySQL paging storage process. I hope you will get some benefits.