MySQL生成連續(xù)的數字/字符/時間序列的方法
前言
有時候為了生成測試數據,或者填充查詢結果中的數據間隔,需要使用到一個連續(xù)的數據序列值。所以,今天我們就來介紹一下如何在 MySQL 中生成連續(xù)的數字、字符以及時間序列值。
使用視圖模擬數值生成器
生成一個連接的數字序列
對于 MySQL 5.7 以及之前的版本,可以通過 UNION 查詢創(chuàng)建一個模擬的數值生成器。例如:
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 個數字。利用相同的方法,我們可以繼續(xù)創(chuàng)建更大的數字生成器。
生成一個間隔的數字序列
如果我們想要通過指定一個增量生成間隔的數字序列,例如 1 到 10 之間的奇數??梢允褂靡韵路绞綄崿F:
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|
生成一個連續(xù)的字符序列
基于以上視圖和 char(n) 函數可以生成連續(xù)的字符序列。例如:
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 的數列,執(zhí)行過程如下:
- 首先,執(zhí)行 CTE 中的初始化查詢,生成一行數據(1);
- 然后,第一次執(zhí)行遞歸查詢,判斷 n < 9,生成一行數據 3(n+2);
- 接著,重復執(zhí)行遞歸查詢,生成更多的數據;直到 n = 9 時不滿足條件終止遞歸;此時臨時表 t 中包含 5 條數據;
- 最后,執(zhí)行主查詢,返回所有的數據。
生成一個等比數字序列
通用表表達式則還可以生成更復雜的數列,例如等比數列:
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 表示下一個斐波那契數列值。
生成一個連續(xù)的字符序列
基于通用表表達式和 CHAR(n) 函數同樣可以生成連續(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 |
生成一個間隔的時間序列
以下語句使用遞歸通用表表達式生成一個時間序列:
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生成連續(xù)的數字/字符/時間序列的方法的文章就介紹到這了,更多相關MySQL生成連續(xù)數字內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
mysql觸發(fā)器(Trigger)簡明總結和使用實例
這篇文章主要介紹了mysql觸發(fā)器(Trigger)簡明總結和使用實例,需要的朋友可以參考下2014-04-04MySql安裝與配置方法(MySQL添加用戶、刪除用戶與授權)
這篇文章主要介紹了MySql安裝與配置方法(MySQL添加用戶、刪除用戶與授權)的相關資料,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2016-08-08使用Grafana+Prometheus監(jiān)控mysql服務性能
這篇文章主要介紹了使用Grafana+Prometheus監(jiān)控mysql服務性能的相關知識,本文給大家介紹的非常詳細,對大家的工作或學習具有一定的參考借鑒價值,需要的朋友可以參考下方法2020-03-03