MySQL 检查每月数据完整性：查找缺失日期

可以使用 MySQL 中的日期函数和子查询来验证每月数据是否完整，以下是一个示例查询：

SELECT DATE_FORMAT(dates.date, '%Y-%m-%d') AS missing_date
FROM (
  SELECT DATE_FORMAT(DATE_ADD(MAKEDATE(YEAR(NOW()), 1), INTERVAL (MONTH(NOW())-1) MONTH), '%Y-%m-%d') AS start_date,
         DATE_FORMAT(DATE_ADD(DATE_ADD(MAKEDATE(YEAR(NOW()), 1), INTERVAL (MONTH(NOW())) MONTH), INTERVAL -1 DAY), '%Y-%m-%d') AS end_date
) AS date_range
CROSS JOIN (
  SELECT DATE_ADD(date_range.start_date, INTERVAL n-1 DAY) AS date
  FROM (
    SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION 
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION 
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION 
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION 
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION 
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION 
    SELECT 31
  ) AS days
  CROSS JOIN date_range
) AS dates
LEFT JOIN (
  SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date
  FROM your_table
  WHERE date >= date_range.start_date AND date <= date_range.end_date
  GROUP BY DATE_FORMAT(date, '%Y-%m-%d')
) AS data ON dates.date = data.date
WHERE data.date IS NULL;

这个查询会生成当前月份的日期范围，并生成每一天的日期，然后使用左连接查找数据表中对应的日期是否存在数据，如果不存在则返回缺少的日期。