MySQL 如何生成连续的数字/字符/时间序列

文章目录

大家好，我是只谈技术不剪发的 Tony 老师。有时候为了生成测试数据，或者填充查询结果中的数据间隔，需要使用到一个连续的数据序列值。所以，今天我们就来介绍一下如何在 MySQL 中生成连续的数字、字符以及时间序列值。

如果你使用的是 Oracle 数据库，实现相同的功能可以参考这篇文章。

如果你使用的是 PostgreSQL，实现相同的功能可以参考这篇文章。

如果你使用的是 SQL Server，实现相同的功能可以参考这篇文章。

使用视图模拟数值生成器 生成一个连接的数字序列

对于 MySQL 5.7 以及之前的版本，可以通过 UNION 查询创建一个模拟的数值生成器。例如：

create or replace view generator10
as select 0 n union all select 1  union all select 2  union all 
   select 3   union all select 4  union all select 5  union all
   select 6   union all select 7  union all select 8  union all
   select 9;

视图 generator10 可以生成从 0 到 9 的 10 个数字。如果我们想要返回一个 1 到 5 的数字序列，可以使用以下查询：

select * from generator10 limit 1, 5;
n|
-|
1|
2|
3|
4|
5|

接下来可以基于这个视图创建其他的数值生成器，用于生成更多的数据。例如：

create or replace view generator100
as select ( tens.n * 10 + ones.n ) as n
   from generator10 ones
   cross join generator10 tens
   order by n;

视图 generator100 可以生成从 0 到 99 的 100 个数字。利用相同的方法，我们可以继续创建更大的数字生成器。

生成一个间隔的数字序列

如果我们想要通过指定一个增量生成间隔的数字序列MySQL 序列使用，例如 1 到 10 之间的奇数。可以使用以下方式实现：

select * from generator100
where n between 1 and 10
and mod(n, 2) = 1

order by n desc;
n|
-|
9|
7|
5|
3|
1|

其中，mod 函数用于返回奇数；order by 用于返回从大到小的序列值。

以下查询返回了一个增量为 2.5、范围从 1.4 到 15 之间的数字序列：

select 1.4 + n*2.5 as n
from generator100
where 1.4 + n*2.5 between 1.4 and 15;
n   |
----|
 1.4|
 3.9|
 6.4|
 8.9|
11.4|
13.9|

另一个方法就是利用 MySQL 中的自定义变量，例如：

select @n:=@n+2.5 as n
from generator100 g, (select @n:= 1.4-2.5) init
where @n+2.5 < 15;
n   |
----|
 1.4|
 3.9|
 6.4|
 8.9|
11.4|
13.9|

生成一个连续的字符序列

基于以上视图和 char(n) 函数可以生成连续的字符序列。例如：

select char(n) 
from generator100
where n between 65 and 70;
char(n)|
-------|
A      |
B      |
C      |
D      |
E      |
F      |

以上查询返回了字符 A 到 F 的序列，char(n) 函数用于将 ASCII 或者 Unicode 编码转化为相应的字符。

生成一个间隔的时间序列

同样基于以上视图和时间加减法可以生成间隔的时间序列。例如：

select ('2020-01-01 00:00:00' + interval n hour) as dt
from generator100
where n between 0 and 12;
dt                 |
-------------------|
2020-01-01 00:00:00|
2020-01-01 01:00:00|
2020-01-01 02:00:00|
2020-01-01 03:00:00|
2020-01-01 04:00:00|
2020-01-01 05:00:00|
2020-01-01 06:00:00|
2020-01-01 07:00:00|
2020-01-01 08:00:00|
2020-01-01 09:00:00|
2020-01-01 10:00:00|
2020-01-01 11:00:00|
2020-01-01 12:00:00|

（编辑：通辽站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!