MySQL是一個廣泛使用的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。在MySQL中，分頁查詢是一個常見的需求。為了更便捷地實現(xiàn)分頁功能，我們可以使用通用分頁存儲過程來簡化程序的編寫。

CREATE PROCEDURE common_page
(
IN tableName varchar(50),
IN condition varchar(200),
IN pageNum int(11),
IN pageSize int(11),
IN orderField varchar(50),
IN orderType int(11),
OUT total int(11)
)
COMMENT '通用分頁存儲過程'
BEGIN
DECLARE sel_sql, count_sql varchar(500);
SET sel_sql = CONCAT('SELECT * FROM ', tableName, ' WHERE 1=1 ');
SET count_sql = CONCAT('SELECT COUNT(*) FROM ', tableName, ' WHERE 1=1 ');
IF condition IS NOT NULL AND condition != '' THEN
SET sel_sql = CONCAT(sel_sql, 'AND ', condition, ' ');
SET count_sql = CONCAT(count_sql, 'AND ', condition, ' ');
END IF;
IF orderField IS NOT NULL AND orderField != '' THEN
SET sel_sql = CONCAT(sel_sql, 'ORDER BY ', orderField, ' ');
END IF;
IF orderType IS NOT NULL THEN
SET sel_sql = CONCAT(sel_sql, orderType = 1 ? ' ASC ' : ' DESC ');
END IF;
SET sel_sql = CONCAT(sel_sql, 'LIMIT ?,?');
PREPARE stmt FROM sel_sql;
SET @limit_start = (pageNum - 1) * pageSize;
SET @limit_offset = pageSize;
EXECUTE stmt USING @limit_start, @limit_offset;
PREPARE stmt2 FROM count_sql;
EXECUTE stmt2 INTO total;
END

通用分頁存儲過程的參數(shù)包括表名、查詢條件、當(dāng)前頁碼、每頁記錄數(shù)、排序字段、排序類型和返回總條數(shù)。存儲過程首先構(gòu)造查詢sql和總記錄數(shù)的sql，并拼接查詢條件和排序方式。然后利用PREPARE和EXECUTE動態(tài)地執(zhí)行SQL語句，傳入計算起始索引和每頁查詢數(shù)量的參數(shù)。

使用通用分頁存儲過程查詢數(shù)據(jù)：

CALL common_page('user', 'age >18', 2, 10, 'birthday', 1, @total);
SELECT @total AS total;

以上示例中，查詢'user'表中年齡大于18歲的用戶數(shù)據(jù)，按照出生日期排序，返回第2頁，每頁10條記錄。通過調(diào)用存儲過程，可以在不同的數(shù)據(jù)表中快速實現(xiàn)分頁查詢功能。