MySQL生成连续数字

MySQL 使用过程当中，经常需要有连续数字的表用来处理一些复杂的报表逻辑，这个blog介绍几种生成连续数字的表方法

如下，我需要生成一张表 id连续的从 1-50

一.自定义变量的方法

构造两个临时表 一个5一个10，通过笛卡尔积可以构造50行记录，即可构造50条记录

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 UNION SELECT 9 UNION SELECT 10) xc2,  
			(SELECT @xi:=0) xc0 

测试记录

mysql>
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 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 U
SELECT 10) xc2,
    ->                  (SELECT @xi:=0) xc0 ;
+------+
| xc   |
+------+
|    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, 2 warnings (0.00 sec)

mysql>

二.存储过程的方法

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

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)

三.MySQL 8.0 With递归方法

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)