MySQL生成数字序列/日期序列

1. MySQL5.7基于自定义变量的方式

生成1-10的连续数字序列：

SELECT @v := @v + 1 AS n 
FROM
(SELECT 1 UNION SELECT 2) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
(SELECT @v := 0) t0

结果：

1
2
3
4
5
6
7
8
9
10

解释：
在from后面，最后一个t0是设置一个初始值@v = 0，而t1与t2的作用仅是利用SQL笛卡尔积的原理，生成10条记录，基于这10条记录，利用变量@v每次加一并更新自身，即可得到1-10的连续数字序列

基于这个方法，构建50以内的奇数序列 [1, 3, 5, ..., 47, 49]：

SELECT @v := @v + 2 AS n 
FROM
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t2,
(SELECT @v := -1) t0

同理，构建最近30天的时间序列：

SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS date 
FROM (
    SELECT @v := @v + 1 AS n from 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) t2,
    (SELECT @v := 0) t0
) t

2. MySQL8基于with递归的方式(CTE, Common Table Expressions)

生成1-10的连续数字序列：

WITH RECURSIVE cte (n) AS
(
    SELECT 1
    UNION ALL 
    SELECT n + 1 FROM cte WHERE n < 10
) 
SELECT n FROM cte

关于MySQL with 用法

结果：

1
2
3
4
5
6
7
8
9
10

3. 基于存储过程的方式

生成1-10的连续数字序列：

DROP TABLE IF EXISTS nums;
CREATE TABLE nums (n INT);

DELIMITER $$

CREATE PROCEDURE build_seq(IN max_n int)
BEGIN
  DECLARE n INT DEFAULT 1;
  WHILE n <= max_n DO
        INSERT INTO nums VALUES(n);
        SET n = n + 1;
    END WHILE;
END $$

DELEMITER ;

CALL build_seq(10);

会生成一张表nums，并生成数据保存在nums中，而不是临时表，结果如下示

SELECT * FROM nums;

1
2
3
4
5
6
7
8
9