MySQL 获取时间轴,跪求优化-优快云博客

本文将探讨并提供优化MySQL中获取时间轴的方法，通过改进现有SQL语句实现性能提升，适合数据库管理和SQL技能提升的学习者。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在搭建数据仓库的过程中像时间,我们经常要获取各种形式的时间轴,oracle很强大我们轻易就可以获取,例如:

SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1
  FROM DUAL
CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'))

mysql中我目前只能这样做,希望达人优化下,顺便提升下我的SQL 能力

SELECT DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY) ,INTERVAL a.idx DAY) FROM 
(SELECT 0 idx
UNION ALL
SELECT 1 idx
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
UNION ALL 
SELECT 10
UNION ALL 
SELECT 10
UNION ALL 
SELECT 11
UNION ALL 
SELECT 12
UNION ALL 
SELECT 13
UNION ALL 
SELECT 14
UNION ALL 
SELECT 15
UNION ALL 
SELECT 16
UNION ALL 
SELECT 17
UNION ALL 
SELECT 18
UNION ALL 
SELECT 19
UNION ALL 
SELECT 20
UNION ALL 
SELECT 21
UNION ALL 
SELECT 22
UNION ALL 
SELECT 23
UNION ALL 
SELECT 24
UNION ALL 
SELECT 25
UNION ALL 
SELECT 26
UNION ALL 
SELECT 27
UNION ALL 
SELECT 28
UNION ALL 
SELECT 29
UNION ALL 
SELECT 30) a  WHERE a.idx<DAY(LAST_DAY(CURDATE()))