MySQL GROUP BY 總行數獲取方法：SQL_CALC_FOUND_ROWS 與 FOUND_ROWS() 經典方案

MySQL GROUP BY 總行數獲取方法：SQL_CALC_FOUND_ROWS 與 FOUND_ROWS() 經典方案

注意：本文內容與文章 175394 高度重複，此處保留核心方案供索引，詳細講解請參考 MySQL 獲取 GROUP BY 總行數的最佳實踐。

核心語法

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff
FROM t1, t2, t3
WHERE (associate t1,t2,and t3 with each other)
GROUP BY t3.id
LIMIT 10, 20;

SELECT FOUND_ROWS() AS total_groups;

機制原理

• SQL_CALC_FOUND_ROWS：指示優化器計算無 LIMIT 時的總行數
• FOUND_ROWS()：返回上一條語句的總行數（忽略 LIMIT）
• 配合 GROUP BY 時，總行數即為**分組數**

基礎 GROUP BY 復習

SELECT column_id, COUNT(*) AS cnt FROM my_table GROUP BY column_id;

模擬行號 (ROW_NUMBER)

SET @rownum = 0;
SELECT @rownum := @rownum + 1 AS row_num, t.*
FROM (SELECT * FROM tbl ORDER BY sort_col) t;

分組後統計記錄條數

SELECT COUNT(*) FROM (
  SELECT COUNT(*) FROM 表名 WHERE 條件 GROUP BY id
) AS a;

版本提示

• MySQL 8.0+ 推薦 COUNT(*) OVER() 窗口函數
• SQL_CALC_FOUND_ROWS 8.0.17 起棄用，未來版本將移除