MySQL 如何生成连续的数字/字符/时间序列_mysql生成时间序列表_不剪发的Tony老师的博客

使用视图模拟数值生成器

生成一个连接的数字序列

对于 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;
接下来可以基于这个视图创建其他的数值生成器，用于生成更多的数据。例如： 
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 个数字。利用相同的方法，我们可以继续创建更大的数字生成器。 
生成一个间隔的数字序列
 
如果我们想要通过指定一个增量生成间隔的数字序列，例如 1 到 10 之间的奇数。可以使用以下方式实现： 
select * from generator100
where n between 1 and 10
and mod(n, 2) = 1
order by n desc;
其中，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;
----|
 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;
----|
 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 到 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;
-------------------|
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;
----------|
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;
以上语句生成了一个从 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;
从第二行开始，每个数字都是上一行的 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|
生成一个间隔的时间序列
 
以下语句使用递归通用表表达式生成一个时间序列： 
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;
-------------------|
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;
----------|
2020-07-15|
2020-07-16|
2020-07-17|
2020-07-18|
2020-07-19|
2020-07-20|
2020-07-21|
如果觉得文章对你有用，欢迎关注❤️、评论📝、点赞👍
                    介绍如何在 MySQL 中生成连续的数字、字符以及时间序列值。包括使用视图模拟生成连接的数字序列、间隔的数字序列、连续的字符序列以及间隔的时间序列，使用通用表表达式生成等差数字序列、等比数字序列、斐波那契数列、连续的字符序列以及间隔的时间序列。
有时候，需要自动生成某一年的日期表(比如用于录入当年的法定节假日,或者其他需要生成一整年只含日期数据的空表,方便以后补充)
例如:需要生成2008年的日期表
2.1. 前期准备
需要一张只包含id为1-500的表，数据如下(表怎么快速生成不做叙述)
select * from t500;
				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
				一、生成数列1.1 生成连续编号首先构建一张 Digits 表，包含数字 0 至 9。Table: DigitsSELECT a.num + (b.num * 10) AS num FROM Digits AS a
CROSS JOIN Digits AS b;1.2 生成部分编号接下来，生成从 1 至 899 的连续编号。SELECT a.num + (b.num * 10) + (c.num ...
mysql> desc nums;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+-------...