mysql生成连续数字或日期

1.自定义变量的方法

mysql数据库生成连续的数字或者基于此生成连续的日期（5.7版本）

业务需要统计近24个月的平均数据，没有数据则补0，需要保证所有日期都有。之前在oracle可以直接根据rownub生成，mysql麻烦一些，需要自定义变量，如下

SELECT 
    @xi:=@xi+1 as xc 
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
    (SELECT @xi:=0) xc0

分析：from后边的 SELECT @xi:=0) xc0 是定义@xi的初始值为0，另外的两个union是为了计算多少个数据

SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5表示五次 两个则是5×5=25个数，即25次

@xi:=@xi:+1表示每次加1.从1开始输出25个数即【1-25】，结果如下

 类似如果想生成【41-80】，则可以修改sql为

SELECT 
    @xi:=@xi+1 as xc
FROM 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
    (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8) xc2,  
    (SELECT @xi:=40) xc0 

如果想生成连续时间可以继续改下sql如下,生成最近24个月的日期

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc MONTH), '%Y-%m') as date
FROM ( 
            SELECT @xi:=@xi+1 as xc from 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1, 
            (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
            (SELECT @xi:=0) xc0 
) xcxc

2.存储过程的方法

写存储过程，这个就会方便很多

drop table t;
create table t(id int);

DELIMITER //

create procedure p_ins_seq(in pi_num int)
BEGIN

   declare l_n1 int default 1;

   truncate table t;

   while l_n1 <= pi_num DO  
     insert into t values (l_n1);
     set l_n1 = l_n1 + 1;
   end while;

end;

DELIMITER  ;

call p_ins_seq(50);

测试记录

mysql>
mysql> drop table t;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t(id int);
Query OK, 0 rows affected (0.02 sec)

mysql>
mysql>
mysql> DELIMITER //
mysql> create procedure p_ins_seq(in pi_num int)
    -> BEGIN
    ->
    ->    declare l_n1 int default 1;
    ->
    ->    truncate table t;
    ->
    ->    while l_n1 <= pi_num DO
    ->      insert into t values (l_n1);
    ->      set l_n1 = l_n1 + 1;
    ->    end while;
    ->
    -> end;
    -> //
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> call p_ins_seq(50);
Query OK, 1 row affected (0.14 sec)

mysql>
mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
|   16 |
|   17 |
|   18 |
|   19 |
|   20 |
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
|   26 |
|   27 |
|   28 |
|   29 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   37 |
|   38 |
|   39 |
|   40 |
|   41 |
|   42 |
|   43 |
|   44 |
|   45 |
|   46 |
|   47 |
|   48 |
|   49 |
|   50 |
+------+
50 rows in set (0.00 sec)

3.With递归方法（MySQL 8.0）

MySQL 8.0开始支持with语法后，这个就大大的简便了

with recursive c(n) AS
(
select 1
union ALL
select n + 1
from c
where n < 50
)
select * from c;

测试记录

mysql> with recursive c(n) AS
    -> (
    -> select 1
    -> union ALL
    -> select n + 1
    -> from c
    -> where n < 50
    -> )
    -> select * from c;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
|   11 |
|   12 |
|   13 |
|   14 |
|   15 |
|   16 |
|   17 |
|   18 |
|   19 |
|   20 |
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
|   26 |
|   27 |
|   28 |
|   29 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   37 |
|   38 |
|   39 |
|   40 |
|   41 |
|   42 |
|   43 |
|   44 |
|   45 |
|   46 |
|   47 |
|   48 |
|   49 |
|   50 |
+------+
50 rows in set (0.00 sec)