如何获取两日期间所有日期并跳过数据库中已存在的日期

我来帮你梳理这个需求的实现方案，分纯MySQL和PHP+MySQL结合两种方式，你可以根据自己的环境和需求选择：

一、纯MySQL方案（推荐MySQL 8.0+） #

如果你的MySQL版本是8.0及以上，用递归CTE（公共表表达式）可以很方便生成连续日期，再排除指定日期，还能关联你的业务表数据。

1. 基础版：生成连续日期并排除指定日期 #

直接生成起始到结束的所有日期，过滤掉不需要的日期：

WITH RECURSIVE date_range AS (
    -- 替换成你的起始日期
    SELECT '2018-03-01' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    -- 替换成你的结束日期
    WHERE date_val < '2018-04-15'
)
SELECT date_val 
FROM date_range
-- 替换成你要排除的日期集合
WHERE date_val NOT IN ('2018-03-11', '2018-04-11')
ORDER BY date_val;

2. 关联业务表版本 #

如果需要同时展示数据库中存在的记录（没有的日期显示NULL），可以用LEFT JOIN：

WITH RECURSIVE date_range AS (
    SELECT '2018-03-01' AS date_val
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2018-04-15'
)
SELECT 
    dr.date_val,
    yt.id,  -- 替换成你的业务表字段
    yt.content  -- 替换成你的业务表字段
FROM date_range dr
LEFT JOIN your_table yt 
    -- 注意：如果业务表是datetime类型，用DATE()转成日期匹配
    ON dr.date_val = DATE(yt.record_date)
WHERE dr.date_val NOT IN ('2018-03-11', '2018-04-11')
ORDER BY dr.date_val;

3. 兼容MySQL 5.x版本 #

如果你的MySQL版本低于8.0，不支持CTE，可以用数字辅助表生成日期：

SELECT DATE_ADD('2018-03-01', INTERVAL num DAY) AS date_val
FROM (
    -- 生成0-999的数字序列，足够覆盖近3年的日期
    SELECT a.n + b.n * 10 + c.n * 100 AS num
    FROM 
    (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
    (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b,
    (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
) nums
WHERE 
    DATE_ADD('2018-03-01', INTERVAL num DAY) <= '2018-04-15'
    AND DATE_ADD('2018-03-01', INTERVAL num DAY) NOT IN ('2018-03-11', '2018-04-11')
ORDER BY date_val;

二、PHP+MySQL结合方案 #

如果需要更灵活的逻辑处理（比如复杂的排除规则），可以用PHP先生成连续日期数组，再过滤排除项，最后关联数据库数据。

1. PHP生成并过滤日期范围 #

// 配置参数
$startDate = new DateTime('2018-03-01');
$endDate = new DateTime('2018-04-15');
$excludeDates = ['2018-03-11', '2018-04-11']; // 要排除的日期

// 生成连续日期并过滤
$dateRange = [];
$currentDate = clone $startDate;
while ($currentDate <= $endDate) {
    $dateStr = $currentDate->format('Y-m-d');
    if (!in_array($dateStr, $excludeDates)) {
        $dateRange[] = $dateStr;
    }
    $currentDate->modify('+1 day');
}

// 输出纯日期（如果不需要数据库数据）
foreach ($dateRange as $date) {
    echo $date . "<br>";
}

2. 关联数据库数据 #

如果需要从数据库获取对应日期的记录，可以用参数化查询避免SQL注入：

// 假设用PDO连接数据库
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8', 'user', 'pass');

if (!empty($dateRange)) {
    // 生成占位符
    $placeholders = implode(',', array_fill(0, count($dateRange), '?'));
    $sql = "SELECT DATE(record_date) AS date_val, id, content FROM your_table WHERE DATE(record_date) IN ($placeholders)";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute($dateRange);
    $dbRecords = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // 将数据库记录和日期范围合并，没有数据的日期对应null
    $result = [];
    foreach ($dateRange as $date) {
        $record = array_filter($dbRecords, function($item) use ($date) {
            return $item['date_val'] == $date;
        });
        $result[] = [
            'date' => $date,
            'data' => !empty($record) ? reset($record) : null
        ];
    }
    
    // 输出结果
    print_r($result);
}

常见问题排查 #

你之前半实现遇到问题，可能是这些坑：

• 日期格式不匹配：如果数据库存的是datetime类型（比如2018-03-11 00:00:00），排除时要确保用DATE(record_date)转成日期，或者排除的字符串带时间部分
• 未生成连续日期：只查询了数据库中存在的日期，导致缺失不存在的日期
• MySQL版本不兼容：用了CTE但版本低于8.0，这时候换数字辅助表方案

内容的提问来源于stack exchange，提问作者zsoro