Mysql

MariaDB,SEQUENCE 引擎,動態上限

  • April 2, 2020

我想在 MariaDB 中生成一個序列,從 1 開始,以其他表中最大的 id 結尾。另一個表大致具有以下架構:

create table main (
 id integer primary key,
 -- ... other columns --
);

我已經了解了用於生成序列的MariaDB STORAGE引擎,但從表面上看,這僅適用於生成靜態序列,您在編寫查詢時知道範圍,例如

MariaDB [skynetdb]> select * from seq_1_to_3;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
+-----+
3 rows in set (0.001 sec)

但是如何生成其上限是結果的序列select max(id) from main?似乎 MariaDB 不接受元查詢,我將在其中構造要查詢的表的名稱,然後在單個查詢中查詢它,例如:

-- INVALID, FOR ILLUSTRATION ONLY
select * from concat('seq_1_to_', select max(id) from main)

或者有可能嗎?如何?(注意我知道我可以做一個額外的往返並在客戶端上構造序列表名稱,但我想避免它,因為我沒有功能豐富的客戶端,只有mysql命令)


以下遞歸公用表表達式 (CTE) 有效

MariaDB [skynetdb]> with recursive nums(n) as (select 1 union all select n+1 from nums where n <= (select max(id) from main)) select * from nums;

但它太慢了。似乎它nums首先在記憶體中構造了整個表。

SELECT seq
   FROM seq_0_to_99999999
   WHERE seq BETWEEN 12345678 and 12345688;

工作正常且快速。它返回 11 行。

更複雜的查詢:

SELECT MIN(FROM_DAYS(seq)) AS first_day,
      MAX(FROM_DAYS(seq)) AS last_day,
      COUNT(*) AS number_of_days
   FROM seq_0_to_99999999
   WHERE seq BETWEEN TO_DAYS('2020-01-01')
                 AND TO_DAYS(CURDATE());

回來:

+------------+------------+----------------+
| first_day  | last_day   | number_of_days |
+------------+------------+----------------+
| 2020-01-01 | 2020-04-02 |             93 |
+------------+------------+----------------+
1 row in set (0.00 sec)

它也很快,根據 ‘Handler_read%’ 值僅觸及 94 行。參考:http : //mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

對於您的情況,您可能需要類似的東西

WHERE seq BETWEEN ...
             AND ( SELECT MAX(...) FROM ... )

我發現 seq 的一般用途是生成大量的值,然後放開WHERE我需要的範圍。優化器避免真正生成我似乎要求的無數值。(至少在這樣的例子中。)

引用自:https://dba.stackexchange.com/questions/262309