我要评论前言
有时候为了生成测试数据，或者填充查询结果中的数据间隔，需要使用到一个连续的数据序列值。所以，今天我们就来介绍一下如何在 mysql 中生成连续的数字、字符以及时间序列值。
使用视图模拟数值生成器
生成一个连接的数字序列
对于 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|
视图 generator100 可以生成从 0 到 99 的 100 个数字。利用相同的方法，我们可以继续创建更大的数字生成器。
生成一个间隔的数字序列
如果我们想要通过指定一个增量生成间隔的数字序列，例如 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|
以上查询返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00、间隔为 1 小时的所有时间点。
以下查询返回了从明天开始一周的日期：
select (current_date + interval n day) as dt from generator100 where n between 1 and 7; dt | ----------| 2020-07-16| 2020-07-17| 2020-07-18| 2020-07-19| 2020-07-20| 2020-07-21| 2020-07-22|
以上方法存在一定的的缺陷，例如可以生成的数据量有限，即使只生成一个很小的数列也需要构建完整的笛卡尔积。
使用通用表表达式生成序列
生成一个等差数字序列
mysql 8.0 中新增的通用表表达式（common table expression）支持递归调用，可以用于生成各种数列。例如：
with recursive t(n) as ( select 1 union all select n+2 from t where n < 9 ) select n from t; n| -| 1| 3| 5| 7| 9|
以上语句生成了一个从 1 递增到 9、增量为 2 的数列，执行过程如下：
- 首先，执行 cte 中的初始化查询，生成一行数据（1）；
- 然后，第一次执行递归查询，判断 n < 9，生成一行数据 3（n+2）；
- 接着，重复执行递归查询，生成更多的数据；直到 n = 9 时不满足条件终止递归；此时临时表 t 中包含 5 条数据；
- 最后，执行主查询，返回所有的数据。
生成一个等比数字序列
通用表表达式则还可以生成更复杂的数列，例如等比数列：
with recursive t(n) as ( select 1 union all select n * 3 from t limit 5 ) select n from t; n | ---| 1| 3| 9| 27| 81|
从第二行开始，每个数字都是上一行的 3 倍。
生成斐波那契数列
斐波那契数列（fibonacci series）是指从数字 0 和 1（或者从 1 和 1）开始，后面的每个数字等于它前面两个数字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表达式可以很容易地生成斐波那契数列：
with recursive fibonacci (n, fib_n, next_fib_n) as ( select 1, 0, 1 union all select n + 1, next_fib_n, fib_n + next_fib_n from fibonacci where n < 10 ) select * from fibonacci; n |fib_n|next_fib_n| --|-----|----------| 1| 0| 1| 2| 1| 1| 3| 1| 2| 4| 2| 3| 5| 3| 5| 6| 5| 8| 7| 8| 13| 8| 13| 21| 9| 21| 34| 10| 34| 55|
其中，字段 n 表示该行包含了第 n 个斐波那契数列值；字段 fib_n 表示斐波那契数列值；字段 next_fib_n 表示下一个斐波那契数列值。
生成一个连续的字符序列
基于通用表表达式和 char(n) 函数同样可以生成连续的字符序列，例如：
with recursive t(n) as ( select 65 union all select n+1 from t where n < 70 ) select char(n) from t; chr| ---| a | b | c | d | e | f |
生成一个间隔的时间序列
以下语句使用递归通用表表达式生成一个时间序列：
with recursive ts(v) as (
select cast('2020-01-01 00:00:00' as datetime)
union all
select v + interval 1 hour from ts where v < '2020-01-01 12:00:00'
)
select * from ts;
v |
-------------------|
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|
以上查询返回了 2020-01-01 00:00:00 到 2020-01-01 12:00:00、间隔为 1 小时的所有时间点。
以下查询返回了从今天开始一周的日期：
with recursive ts(v) as ( select current_date union all select v + 1 from ts limit 7 ) select * from ts; v | ----------| 2020-07-15| 2020-07-16| 2020-07-17| 2020-07-18| 2020-07-19| 2020-07-20| 2020-07-21|
到此这篇关于mysql生成连续的数字/字符/时间序列的方法的文章就介绍到这了,更多相关mysql生成连续数字内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论