MySQL生成连续数字

偏执的太偏执、 2022-12-15 11:21 294阅读 0赞

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

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

### 文章目录 ###

*  一.自定义变量的方法
 *  二.存储过程的方法
 *  三.MySQL 8.0 With递归方法

# 一.自定义变量的方法 #

构造两个临时表 一个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)