個(gè)人中心
退出
MySQL 如何生成連續(xù)的數(shù)字/字符/時(shí)間序列
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
使用視圖模擬數(shù)值生成器
生成一個(gè)連接的數(shù)字序列
生成一個(gè)間隔的數(shù)字序列
生成一個(gè)連續(xù)的字符序列
生成一個(gè)間隔的時(shí)間序列
使用通用表表達(dá)式生成序列
生成一個(gè)等差數(shù)字序列
生成一個(gè)等比數(shù)字序列
生成斐波那契數(shù)列
生成一個(gè)連續(xù)的字符序列
生成一個(gè)間隔的時(shí)間序列
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。有時(shí)候?yàn)榱松蓽y(cè)試數(shù)據(jù)，或者填充查詢結(jié)果中的數(shù)據(jù)間隔，需要使用到一個(gè)連續(xù)的數(shù)據(jù)序列值。所以，今天我們就來介紹一下如何在 MySQL 中生成連續(xù)的數(shù)字、字符以及時(shí)間序列值。
??如果你使用的是 Oracle 數(shù)據(jù)庫，實(shí)現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 PostgreSQL，實(shí)現(xiàn)相同的功能可以參考這篇文章。
??如果你使用的是 SQL Server，實(shí)現(xiàn)相同的功能可以參考這篇文章。
使用視圖模擬數(shù)值生成器
生成一個(gè)連接的數(shù)字序列
對(duì)于 MySQL 5.7 以及之前的版本，可以通過 UNION 查詢創(chuàng)建一個(gè)模擬的數(shù)值生成器。例如：
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 個(gè)數(shù)字。如果我們想要返回一個(gè) 1 到 5 的數(shù)字序列，可以使用以下查詢：
select * from generator10 limit 1, 5;
n|
-|
1|
2|
3|
4|
5|
接下來可以基于這個(gè)視圖創(chuàng)建其他的數(shù)值生成器，用于生成更多的數(shù)據(jù)。例如：
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 個(gè)數(shù)字。利用相同的方法，我們可以繼續(xù)創(chuàng)建更大的數(shù)字生成器。
生成一個(gè)間隔的數(shù)字序列
如果我們想要通過指定一個(gè)增量生成間隔的數(shù)字序列，例如 1 到 10 之間的奇數(shù)?？梢允褂靡韵路绞綄?shí)現(xiàn)：
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 函數(shù)用于返回奇數(shù)；order by 用于返回從大到小的序列值。
以下查詢返回了一個(gè)增量為 2.5、范圍從 1.4 到 15 之間的數(shù)字序列：
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|
另一個(gè)方法就是利用 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|
生成一個(gè)連續(xù)的字符序列
基于以上視圖和 char(n) 函數(shù)可以生成連續(xù)的字符序列。例如：
select char(n)
from generator100
where n between 65 and 70;
char(n)|
-------|
A |
B |
C |
D |
E |
F |
以上查詢返回了字符 A 到 F 的序列，char(n) 函數(shù)用于將 ASCII 或者 Unicode 編碼轉(zhuǎn)化為相應(yīng)的字符。
生成一個(gè)間隔的時(shí)間序列
同樣基于以上視圖和時(shí)間加減法可以生成間隔的時(shí)間序列。例如：
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 小時(shí)的所有時(shí)間點(diǎn)。
以下查詢返回了從明天開始一周的日期：
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|
以上方法存在一定的的缺陷，例如可以生成的數(shù)據(jù)量有限，即使只生成一個(gè)很小的數(shù)列也需要構(gòu)建完整的笛卡爾積。
使用通用表表達(dá)式生成序列
生成一個(gè)等差數(shù)字序列
MySQL 8.0 中新增的通用表表達(dá)式（Common Table Expression）支持遞歸調(diào)用，可以用于生成各種數(shù)列。例如：
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|
以上語句生成了一個(gè)從 1 遞增到 9、增量為 2 的數(shù)列，執(zhí)行過程如下：
首先，執(zhí)行 CTE 中的初始化查詢，生成一行數(shù)據(jù)（1）；
然后，第一次執(zhí)行遞歸查詢，判斷 n < 9，生成一行數(shù)據(jù) 3（n+2）；
接著，重復(fù)執(zhí)行遞歸查詢，生成更多的數(shù)據(jù)；直到 n = 9 時(shí)不滿足條件終止遞歸；此時(shí)臨時(shí)表 t 中包含 5 條數(shù)據(jù)；
最后，執(zhí)行主查詢，返回所有的數(shù)據(jù)。
生成一個(gè)等比數(shù)字序列
通用表表達(dá)式則還可以生成更復(fù)雜的數(shù)列，例如等比數(shù)列：
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|
從第二行開始，每個(gè)數(shù)字都是上一行的 3 倍。
生成斐波那契數(shù)列
斐波那契數(shù)列（Fibonacci series）是指從數(shù)字 0 和 1（或者從 1 和 1）開始，后面的每個(gè)數(shù)字等于它前面兩個(gè)數(shù)字之和（0、1、1、2、3、5、8、13、21、…）。使用通用表表達(dá)式可以很容易地生成斐波那契數(shù)列：
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 個(gè)斐波那契數(shù)列值；字段 fib_n 表示斐波那契數(shù)列值；字段 next_fib_n 表示下一個(gè)斐波那契數(shù)列值。
生成一個(gè)連續(xù)的字符序列
基于通用表表達(dá)式和 CHAR(n) 函數(shù)同樣可以生成連續(xù)的字符序列，例如：
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 |
生成一個(gè)間隔的時(shí)間序列
以下語句使用遞歸通用表表達(dá)式生成一個(gè)時(shí)間序列：
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 小時(shí)的所有時(shí)間點(diǎn)。
以下查詢返回了從今天開始一周的日期：
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|
如果覺得文章對(duì)你有用，歡迎關(guān)注??、評(píng)論??、點(diǎn)贊??
分類導(dǎo)航