mysql 存在连续数字_MySQL生成连续数字

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

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

Table of Contents

一.自定义变量的方法

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