在MySQL中生成数字序列

原创小小亮 2020-07-28

4652

在MySQL中生成整数序列的最简单方法是什么？换句话说，我应该写哪个SELECT <something>语句以获得0、1、2…N – 1？

这是我多年来一直在苦苦挣扎的问题，看起来我终于找到了答案（尽管我必须承认我必须付出一些开发努力，并在服务器代码中添加几百行）。适用于MySQL 8.0.20-11的Percona Server包含专门用于解决此问题的新功能。

但是，在揭示所有秘密之前，让我们首先考虑现有的解决方案。因此，我们希望获得以下信息：

SELECT ???
+-------+
| value |
+-------+
|     0 |
|     1 |
|   ... |
| N - 1 |
+-------+
N rows in set (0.00 sec)
复制

我们有什么选择呢？

老派方式

让我们从最简单的解决方案开始。

UNION to the Rescue

听起来有些原始，但最简单的解决方案是将多个SELECT语句的结果与UNION合并为单个结果集。

SELECT 0 AS value UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

尽管这可能适用于较小的数字，但是此解决方案不是很可扩展。

具有唯一列的现有表

假设我们已经有了一个包含任何数据类型的唯一列的表。例如：

CREATE TABLE t1(id CHAR NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
 
INSERT INTO t1 VALUES ('a'), ('b'), ('c'), ('d');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
SELECT * FROM t1;
+----+
| id |
+----+
| a  |
| b  |
| c  |
| d  |
+----+
4 rows in set (0.00 sec)
复制

现在，我们可以将此表与其自身的副本连接起来，并选择副本中的id小于或等于原始表中的id的记录数。

SELECT COUNT(*) - 1 AS value FROM t1, t1 AS t2 WHERE t2.id <= t1.id GROUP BY t1.id;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
复制

该解决方案的主要缺点是其在N上的二次复杂度，当N大时可能导致大量资源利用。

SELECT中的会话变量增量

假设我们已经像上一个示例一样具有表t1（尽管此处不需要唯一列约束），则可以将其与单个值SELECT联接，该值将初始值分配给会话变量。同时，对于现有表的每个记录，它将增加该会话变量的值。

SELECT (@val := @val + 1) - 1 AS value FROM t1, (SELECT @val := 0) AS tt;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set, 2 warnings (0.00 sec)
复制

这个还不错：它是可扩展的，N上的线性复杂度不会引入不必要的开销，唯一的缺点是需要有一个现有表。

联接多个视图

我们始终可以联接包含多个记录的多个表（或视图），以将结果集中的记录总数相乘。

CREATE VIEW binary_v AS SELECT 0 AS v UNION ALL SELECT 1;
Query OK, 0 rows affected (0.00 sec)
 
SELECT * FROM binary_v ORDER BY v;
+---+
| v |
+---+
| 0 |
| 1 |
+---+
2 rows in set (0.00 sec)
 
SELECT b0.v + b1.v * 2 + b2.v * 4 AS value FROM binary_v b0, binary_v b1, binary_v b2 ORDER BY value;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
|     7 |
+-------+
8 rows in set (0.00 sec)
复制

使用相同的方法，通过相交K个binary_v实例，我们可以生成2 ^ K个值的序列。同样，我们可以为数字创建一个视图，结果得到10 ^ K的值。


CREATE VIEW decimal_v AS SELECT 0 AS v 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;
Query OK, 0 rows affected (0.00 sec)
 
SELECT * FROM decimal_v ORDER by v;
+---+
| v |
+---+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---+
10 rows in set (0.00 sec)
 
SELECT d0.v + d1.v * 10 + d2.v * 100 AS value 
  FROM decimal_v d0, decimal_v d1, decimal_v d2 ORDER BY value;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|   ... |
|   998 |
|   999 |
+-------+
1000 rows in set (0.01 sec)

复制

尽管这似乎很容易理解，但是这种查询的执行计划肯定还远非完美。

古典主义

存储过程

基本上，在选择之前，我们可以创建一个临时表，并使用预先创建的存储过程为其填充所需的数字。

CREATE TEMPORARY TABLE t1 (value BIGINT UNSIGNED NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
 
CALL generate_seq(4);
Query OK, 1 row affected (0.01 sec)
 
SELECT * FROM t1 ORDER BY value;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

存储过程本身可以定义如下：

delimiter |
CREATE PROCEDURE generate_seq(n BIGINT UNSIGNED)
BEGIN
  DECLARE i BIGINT UNSIGNED DEFAULT 0;
  WHILE i < n DO
    INSERT INTO t1 VALUES(i);
    SET i = i + 1;
  END WHILE;
END|
delimiter ;
复制

这种方法的执行计划几乎是完美的，唯一的缺点是在使用序列之前必须调用generate_seq（）。

准备的陈述

让我们尝试自动化基于UNION的解决方案。无需手动重复UNION子句，我们可以动态生成此语句。

SET @generated_stmt = generate_seq_stmt(4);
Query OK, 0 rows affected (0.00 sec)
 
PREPARE stmt1 FROM @generated_stmt;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
 
EXECUTE stmt1;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
 
DEALLOCATE PREPARE stmt1;
Query OK, 0 rows affected (0.00 sec)
复制

其中generate_seq_stmt（）可以定义如下：

delimiter |
CREATE FUNCTION generate_seq_stmt(n BIGINT UNSIGNED) RETURNS TEXT DETERMINISTIC
BEGIN
  DECLARE res TEXT DEFAULT 'SELECT 0 AS value';
  DECLARE i BIGINT UNSIGNED DEFAULT 1;
  WHILE i < n DO
    SET res = CONCAT(res, ' UNION ALL SELECT ', i);
    SET i = i + 1;
  END WHILE;
  RETURN res;
END|
delimiter ;
复制

这似乎可行，但是此解决方案的主要缺点是无法将其直接嵌入更复杂的查询中（除非后者也转换为准备好的语句）。

序列存储引擎（MariaDB）

另一方面，MariaDB采用了完全不同的方法。他们没有扩展SQL语法并发明新的结构，而是在版本10.0中实现了Sequence Storage Engine，该序列存储引擎在用户需要时自动创建完全虚拟的临时表。您所要做的就是执行以下操作：

SELECT * FROM seq_0_to_3;
+-----+
| seq |
+-----+
|   0 |
|   1 |
|   2 |
|   3 |
+-----+
4 rows in set (0.00 sec)
复制

但是，这似乎很清楚，但是为每个数据库保留几乎无限数量的表名（seq_ _ 和seq_ _ _ ）的想法并不吸引人。例如，您不能执行以下操作：

CREATE TABLE seq_1_to_100 (col INT) ENGINE = InnoDB;
ERROR 1050 (42S01): Table 'seq_1_to_100' already exists
复制

这种方法需要大量不必要的错误处理和服务器代码中的其他分支。他们的文档中还提到了一些技巧（例如ALTER TABLE seq_1_to_100 ENGINE = BLACKHOLE），可以用来解决某些问题，但总的来说，它们只会增加更多的复杂性和极端情况。
就个人而言，我喜欢语法SELECT * FROM <virtual_sequence_generator>，但是将这种结构实现为新的存储引擎并不是最佳的设计决定。

现代方式

递归公用表表达式（CTE）

在MySQL Server 8.0.1中，Oracle引入了非递归和递归通用表表达式（CTE）。

WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq WHERE value < 3)
  SELECT * FROM seq;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

尽管此解决方案可用于很大范围的上限N，并且似乎正是我们需要的上限，但我怀疑它是否可读/易于理解。

从MySQL Server 8.0.19开始，您可以使用`LIMIT`代替`WHERE`来简化此查询。

WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq LIMIT 4)
  SELECT * FROM seq;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

但是，这两种解决方案都有局限性。缺省情况下，上限N不能很高。

WITH RECURSIVE seq AS (SELECT 0 AS value UNION ALL SELECT value + 1 FROM seq LIMIT 1002)
  SELECT * FROM seq;
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
复制

但是，增加cte_max_recursion_depth可以改变此限制。

值行（…），行（…）…

如果您很幸运，已经升级到MySQL Server 8.0.19，则可以使用VALUES语句（一个表值构造函数，它也可以用作独立的SQL语句）。

VALUES ROW(0), ROW(1), ROW(2), ROW(3);
+----------+
| column_0 |
+----------+
|        0 |
|        1 |
|        2 |
|        3 |
+----------+
4 rows in set (0.00 sec)
复制

这个比基于UNION的要容易一些，但是仍然缺乏可扩展性。

JSON_TABLE（）

在MySQL Server 8.0.4中， Oracle引入了一个新的JSON_TABLE（）函数，该函数可以从JSON文档中提取数据并将其作为具有指定列的关系表返回。但是您可能会问，这甚至可能与生成数字序列有关。让我们考虑以下示例。

SELECT *
 FROM JSON_TABLE('[{"a":0},{"a":1},{"a":2},{"a":3}]',
                 "$[*]" COLUMNS(value BIGINT UNSIGNED PATH "$.a")
      ) AS tt;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

在这里，我们将一个简单的JSON文档传递给JSON_TABLE（）函数。该JSON文档是一个由一系列对象组成的数组，这些对象具有预定义的键“ a”。我们遍历数组中与“ $ [*]” JSON路径表达式匹配的所有元素，并将“ $ .a”键的值提取到名称为value的BIGINT UNSIGNED类型的列中。尽管此时语法开销不堪重负，但我开始看到隧道尽头的光芒。

我们可以做得更好，并改进我们的第一个JSON_TABLE（）示例。

SELECT tt.rowid - 1 AS value
  FROM JSON_TABLE('[{},{},{},{}]',
                  "$[*]" COLUMNS(rowid FOR ORDINALITY)
       ) AS tt;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

在这里，我们有一个空对象数组，并使用特殊的FOR ORDINALITY构造，该构造等效于在CREATE TABLE语句中将列指定为AUTO_INCREMENT。尽管我应该注意，我们数组中仍然有预定义数量的空JSON对象。这还不够–我们必须更深入。

SET @upper_bound = 4;
Query OK, 0 rows affected (0.00 sec)
 
SELECT tt.rowid - 1 AS value
  FROM JSON_TABLE(CONCAT('[{}', REPEAT(',{}', @upper_bound - 1), ']'),
                  "$[*]" COLUMNS(rowid FOR ORDINALITY)
       ) AS tt;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

我们就快到了！

后现代方式

让我们首先总结一下在上一个示例中成功实现的目标。

清除语法构造SELECT…FROM JSON_TABLE（…）AS tt（尽管函数参数仍然很重要）
我们使此构造根据@upper_bound变量的值生成不同数量的行。
可以在允许任何其他派生表语句的任何地方使用此构造。
在连接表的情况下，此构造不仅可以依赖于会话变量的值，而且还可以根据来自另一个表的列的值生成不同数量的行。

SEQUENCE_TABLE（）

用一个理想的词来说，拥有一个与JSON_TABLE（CONCAT（'[{}'，REPEAT（'，{}'，@upper_bound – 1），']相同的功能，例如SEQUENCE_TABLE（）真的很棒） “），‘$ [*]’列（ROWID FOR序数）。
不幸的是，MySQL服务器8.0.19不具备这样的功能。

我必须承认，在这一点上，通过说“ 基于JSON_TABLE（）的解决方案是到目前为止最好的解决方案，这对我来说真的很糟糕。这就是我们所能做的，感谢您阅读 “，所以我不会这样做。
相反，我要宣布，在Percona Server 8.0.20-11中，我们实现了一个新的Percona特定功能，称为SEQUENCE_TABLE（）。

长话短说，现在您可以编写以下内容。

SELECT * FROM SEQUENCE_TABLE(4) AS tt;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

是的，就像您看到的一样简单和直接。现在，让我们考虑一些更复杂的示例。

如果我们要生成一个从4到7（含）的序列怎么办？

SELECT value AS result FROM SEQUENCE_TABLE(8) AS tt WHERE value >= 4;
+--------+
| result |
+--------+
|      4 |
|      5 |
|      6 |
|      7 |
+--------+
4 rows in set (0.00 sec)
复制

或者，您可以编写：

SELECT value + 4 AS result FROM SEQUENCE_TABLE(4) AS tt;
+--------+
| result |
+--------+
|      4 |
|      5 |
|      6 |
|      7 |
+--------+
4 rows in set (0.00 sec)
复制

另一个示例，从0到6的偶数（包括0和6）：

SELECT value AS result FROM SEQUENCE_TABLE(8) AS tt WHERE value % 2 = 0;
+--------+
| result |
+--------+
|      0 |
|      2 |
|      4 |
|      6 |
+--------+
4 rows in set (0.00 sec)
复制

或者：

SELECT value * 2 AS result FROM SEQUENCE_TABLE(4) AS tt;
+--------+
| result |
+--------+
|      0 |
|      2 |
|      4 |
|      6 |
+--------+
4 rows in set (0.00 sec)
复制

又一个示例，数字从0到3包含相反的顺序：

SELECT value AS result FROM SEQUENCE_TABLE(4) AS tt ORDER BY value DESC;
+--------+
| result |
+--------+
|      3 |
|      2 |
|      1 |
|      0 |
+--------+
4 rows in set (0.00 sec)
复制

或者：

SELECT 3 - value AS result FROM SEQUENCE_TABLE(4) AS tt;
+--------+
| result |
+--------+
|      3 |
|      2 |
|      1 |
|      0 |
+--------+
4 rows in set (0.00 sec)
复制

SEQUENCE_TABLE（）也可用于生成一组随机数：

SELECT FLOOR(RAND() * 100) AS result FROM SEQUENCE_TABLE(4) AS tt;
+--------+
| result |
+--------+
|      6 |
|     37 |
|     67 |
|     25 |
+--------+
4 rows in set (0.00 sec)
复制

请注意，SEQUENCE_TABLE（）的使用模式不仅限于数字。例如，我们可以生成预定义字符串文字的列表（如果需要，可以将行转换为列）。

SELECT ELT(value + 1, 'a', 'b', 'c', 'd') AS result FROM SEQUENCE_TABLE(4) AS tt;
+--------+
| result |
+--------+
| a      |
| b      |
| c      |
| d      |
+--------+
4 rows in set (0.00 sec)
复制

或相同，但重复的值：

SELECT ELT(value % 4 + 1, 'a', 'b', 'c', 'd') AS result FROM SEQUENCE_TABLE(8) AS tt;
+--------+
| result |
+--------+
| a      |
| b      |
| c      |
| d      |
| a      |
| b      |
| c      |
| d      |
+--------+
8 rows in set (0.00 sec)
复制

最后，此表函数还可以帮助生成伪随机字符串值：

SELECT MD5(value) AS result FROM SEQUENCE_TABLE(4) AS tt;
+----------------------------------+
| result                           |
+----------------------------------+
| cfcd208495d565ef66e7dff9f98764da |
| c4ca4238a0b923820dcc509a6f75849b |
| c81e728d9d4c2f636f067f89cc14862c |
| eccbc87e4b5ce2fe28308fd9f2a7baf3 |
+----------------------------------+
4 rows in set (0.00 sec)
复制

此构造可用于填充现有表：

CREATE TABLE t1 (id BIGINT UNSIGNED);
Query OK, 0 rows affected (0.00 sec)
 
INSERT INTO t1 SELECT * FROM SEQUENCE_TABLE(4) AS tt;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
SELECT * FROM t1;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
|    3 |
+------+
4 rows in set (0.00 sec)
复制

甚至创建一个具有预填充值的新值：

CREATE TABLE t1 AS SELECT * FROM SEQUENCE_TABLE(4) AS tt;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
SELECT * FROM t1;
+-------+
| value |
+-------+
|     0 |
|     1 |
|     2 |
|     3 |
+-------+
4 rows in set (0.00 sec)
复制

我很确定还有很多其他用例（例如，生成斐波那契数或在给定范围内打印所有素数），您肯定可以找到很多自己的用例。

结论

在这篇博客文章中，我试图证明SQL是一种非常强大的语言，它使我们能够做很多奇特的事情。但是，令人沮丧的是，在某些非常简单的情况下，唯一要做的事情就是使用重型火炮。我演示的带有SEQUENCE_TABLE（）的示例表明，如果仅通过扩展服务器代码来添加新功能是唯一的选择，那就不要害怕-可以这样做，尤其是当您知道该怎么做时。

作者：Yura Sorokin
文章来源：https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者