mysql按月获取日期

-- 获取某月的所有日期
SELECT DATE_ADD('2023-03-01', INTERVAL n DAY) AS date
FROM (
    SELECT @rownum:=@rownum+1 AS n
    FROM (SELECT 0 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) r,
         (SELECT 0 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) s,
         (SELECT @rownum:=0) t
) d
WHERE DATE_ADD('2023-03-01', INTERVAL n DAY) <= '2023-03-31';

SELECT DISTINCT DATE_ADD('2023-03-01', INTERVAL n DAY) AS date
FROM (
    SELECT @rownum:=@rownum+1 AS n
    FROM (SELECT 0 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) r,
         (SELECT 0 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) s,
         (SELECT @rownum:=0) t
) d
WHERE DATE_ADD('2023-03-01', INTERVAL n DAY) <= '2023-03-31';

-- 使用索引优化查询
CREATE INDEX idx_date ON your_table(date_column);

-- 分页查询
SELECT DATE_ADD('2023-03-01', INTERVAL n DAY) AS date
FROM (
    SELECT @rownum:=@rownum+1 AS n
    FROM (SELECT 0 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) r,
         (SELECT 0 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) s,
         (SELECT @rownum:=0) t
) d
WHERE DATE_ADD('2023-03-01', INTERVAL n DAY) <= '2023-03-31'
LIMIT 10 OFFSET 0;