MariaDB 動態產生資料行

如果臨時需要一連串的資料，例如 1 到 10 之類的數字，又或是英文 A 到 Z 的 26 筆資料，該如何產生？難道要用一堆 UNION 嗎？還是建立一張臨時資料表？

其實各個不同的資料庫都有各自動態產生資料行的方式，例如 oracle 就可以用下面這個例子產生：

  
select level from dual connect by level <= 10;
  

在 MariaDB 10.1 以後，內建一個新的「SEQUENCE」套件，可以產生有順序的資料行，日期和數字都可以

確認 SEQUENCE 套件是否安裝

要查看是否安裝該套件，可以使用下面這行程式碼：

  
SHOW ENGINES;
  

輸出內容：
如果有下面範例輸出的第 11 行，就代表可以使用接下來要介紹的語法，如果沒有的話也可以手動安裝

  
SHOW ENGINES;

+------------------+-------+-----------------------------------------------------------------------------------------------+------------+---+----------+
|Engine            |Support|Comment                                                                                        |Transactions|XA |Savepoints|
+------------------+-------+-----------------------------------------------------------------------------------------------+------------+---+----------+
|CSV               |YES    |Stores tables as CSV files                                                                     |NO          |NO |NO        |
|MRG_MyISAM        |YES    |Collection of identical MyISAM tables                                                          |NO          |NO |NO        |
|MEMORY            |YES    |Hash based, stored in memory, useful for temporary tables                                      |NO          |NO |NO        |
|Aria              |YES    |Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables|NO          |NO |NO        |
|MyISAM            |YES    |Non-transactional engine with good performance and small data footprint                        |NO          |NO |NO        |
|SEQUENCE          |YES    |Generated tables filled with sequential values                                                 |YES         |NO |YES       |
|InnoDB            |DEFAULT|Supports transactions, row-level locking, foreign keys and encryption for tables               |YES         |YES|YES       |
|PERFORMANCE_SCHEMA|YES    |Performance Schema                                                                             |NO          |NO |NO        |
+------------------+-------+-----------------------------------------------------------------------------------------------+------------+---+----------+
  

手動安裝 SEQUENCE 套件

手動安裝：

  
      INSTALL PLUGIN sequence SONAME 'ha_sequence.so';
  

動態產生資料的各種範例

產生連續數字 1-10

  
SELECT * FROM seq_1_to_10;
  

範例輸出：

  
SELECT * FROM seq_1_to_10;

+---+
|seq|
+---+
|1  |
|2  |
|3  |
|4  |
|5  |
|6  |
|7  |
|8  |
|9  |
|10 |
+---+
  

產生等差數列 1,4,7,9,13,15

  
SELECT * FROM seq_1_to_15_step_3;
  

範例輸出：

  
SELECT * FROM seq_1_to_15_step_3;

+---+
|seq|
+---+
|1  |
|4  |
|7  |
|10 |
|13 |
+---+
  

英文 A-Z

一樣是使用數列，但是把他轉為 ASCII 輸出英文

  
SELECT char(seq) FROM seq_65_to_90 ;

範例輸出：

  
SELECT char(seq) FROM seq_65_to_90 ;

+---------+
|char(seq)|
+---------+
|A        |
|B        |
|C        |
|D        |
...
|Y        |
|Z        |
+---------+
  

產生連續時間

早上 8 點到下午 5 點
註：還可替換為其他單位，如：HOUR, MINUTE, SECOND, MICROSECOND 等

  
SELECT '2022-04-08 ' + INTERVAL (seq) HOUR  AS date FROM seq_8_to_17;
  

範例輸出：

  
SELECT '2022-04-08 ' + INTERVAL (seq) HOUR  AS date FROM seq_8_to_17;

+-------------------+
|date               |
+-------------------+
|2022-04-08 08:00:00|
|2022-04-08 09:00:00|
|2022-04-08 10:00:00|
|2022-04-08 11:00:00|
|2022-04-08 12:00:00|
|2022-04-08 13:00:00|
|2022-04-08 14:00:00|
|2022-04-08 15:00:00|
|2022-04-08 16:00:00|
|2022-04-08 17:00:00|
+-------------------+
  

日期序列

產生連續的日期

  
SELECT '2022-01-01' + INTERVAL (seq) DAY FROM seq_0_to_30;

範例輸出：

  
SELECT '2022-01-01' + INTERVAL (seq) DAY FROM seq_0_to_30;

+---------------------------------+
|'2022-01-01' + INTERVAL (seq) DAY|
+---------------------------------+
|2022-01-01                       |
|2022-01-02                       |
|2022-01-03                       |
    ...
|2022-01-29                       |
|2022-01-30                       |
|2022-01-31                       |
+---------------------------------+
  

參考連結：mariaDB