在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_
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/
