MySQL 统计单表增量数据

按天查看表的增量数据：
SELECT
DATE(create_time) as date,
COUNT(*) as daily_increment
FROM
table_name
WHERE
create_time >= '2024-01-01'
AND create_time < '2024-12-16' + INTERVAL 1 DAY
GROUP BY
date;

SELECT
DATE_FORMAT(create_time, '%Y-%m') AS month,
COUNT(*) AS record_count,
MIN(DATE(create_time)) AS first_day,
MAX(DATE(create_time)) AS last_day
FROM user_info_0
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY month DESC;

 

按月查看表的增量数据，以及增量数据大小：

SELECT
DATE_FORMAT(create_time, '%Y-%m') AS month,
COUNT(*) AS record_count,
-- 估算大小（假设平均行大小200字节，可根据实际调整）
ROUND(COUNT(*) * 200 / 1024 / 1024, 2) AS estimated_size_mb,
-- 更精确的估算（使用表的平均行长度）
ROUND(COUNT(*) * (
SELECT AVG_ROW_LENGTH
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name = 'table_name'
) / 1024 / 1024, 2) AS precise_size_mb
FROM table_name
WHERE create_time >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY month DESC;

 

按小时查看表的增量数据：
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d %H:00:00') as hour,
COUNT(*) as incremental_count
FROM
table_name
WHERE
create_time >= '2024-12-01 00:00:00' AND
create_time <= '2024-12-16 23:59:59'
GROUP BY
hour
ORDER BY
hour;