在MySQL中生成整数序列

Question

我需要与一个包含整数n到m(包括n和m)的表/结果集进行连接。是否有一种简单的方法可以避免手动构建该表？

(顺便问一下，这种类型的结构被称为"元查询"吗?)

m-n的值被限制在合理范围内（小于1000）。

- BCS

---

当需要同时包含父级和子级时，使用auto_increment可能会遇到问题。我从未使用过它，nextval更简单。您可以在此处查看MySQL代码中的nextval函数：http://stackoverflow.com/questions/8058675/error-in-mysql-bigint-variable-declaration-inside-custom-nextval-function - user1041554

Answer 1

我在网上找到了这个解决方案。

SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

单个查询，快速，并且完全符合我的要求：现在我可以使用唯一数字从1开始对复杂查询中找到的“选择”进行编号，并且每一行结果都会递增一次。

我认为这也适用于上面列出的问题：调整@row的初始起始值并添加限制子句以设置最大值。

顺便说一下：我认为“r”实际上并不需要。

ddsp

- David Poor

---

需要 "yes，需要 'r'" - 错误1248：每个衍生表都必须有自己的别名。 - Unreason

所选行数是来自于 some_table 而不是 r。如果 some_table 没有任何行，则什么都不会返回。我使用这个方法生成了一组测试数据，只需要用户名和密码。序列号变成了用户名，密码只是 ENCRYPT('passwd')，以便它们都相同。为了生成行，我从另一个表中进行选择，但实际上并没有选择任何列。它只是给我每个在 some_table 中的行都提供了一个行，所有行都具有连续的数字。 - Mnebuerquo

有些震惊，没想到MySQL可以做到这一点。这两个分开的查询可以工作，例如用于重新排序表的子集：SET @row := 0 和 UPDATE foo SET position = (@row := @row + 1) WHERE <条件> ORDER BY last_modified ASC。请注意，我的翻译可能不是字面上的翻译，但它们保留了原始内容的含义，并尽力使其更加易懂。 - Izkata

从C#执行这个命令会出现错误，"必须定义参数'@row'。" 但我在这里找到了一个解决方案：https://dev59.com/CXNA5IYBdhLWcg3wcddk - EricP

@Izkata，David，这种行为是否被规范保证？你们知道的，将来的版本可能会为所有行提供相同的值，并且仅在整个选择后进行增量。 - Pacerier

Answer 2

以下代码将返回1到10000的数字，速度较快。

SELECT @row := @row + 1 AS row FROM 
(select 0 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) t,
(select 0 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) t2, 
(select 0 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) t3, 
(select 0 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) t4, 
(SELECT @row:=0) numbers;

- Unreason

---

“t - t4” 分别包含 10 行的虚拟数据，它们的笛卡尔积为 10^4，即 10000 行的虚拟数据。而外部 SELECT 是 MySQL 版本的行号。 - Aprillion

@Aprillion，2^14将会做同样的事情。只需要28个值而不是40个。 - Pacerier

你可以通过将“@row := @row + 1 as row”替换为“concat(t.0,t2.0,t3.0,t4.0) + 1 as row”，然后按“row”排序来摆脱变量声明。为了获得准确的结果，您还需要将每个子查询中重复的“select 6”更改为“select 2”（“2”目前未使用，但“6”列出两次）。作为奖励，您还可以将每个“union all”缩短为“union”，因为不应再有任何重复行。 - Seth McCauley

@Seth McCauley：不需要将6改为2。这些值甚至没有被使用。最好全部都是null。只有@row := @row + 1 as row是必要的，以获得每行递增的数字。联合只是乘以给出行数的数字。 - skb

这最初对我失败了，出现了错误“每个派生表都必须有自己的别名”。只需给最终的SELECT添加一个别名即可轻松解决 - 例如，在最后添加t5。 - Steve Chambers

我不得不将“AS row”更改为“AS r”以防止语法错误。 - ijt

Answer 3

如果您正在使用MySQL的MariaDB分支，则SEQUENCE引擎允许直接生成数字序列。它通过使用虚拟（假）单列表来实现这一点。

例如，要生成从1到1000的整数序列，请执行以下操作：

     SELECT seq FROM seq_1_to_1000;

从0到11，执行此操作。

     SELECT seq FROM seq_0_to_11;

从今天开始连续一周的日期值，请执行此操作。

SELECT FROM_DAYS(seq + TO_DAYS(CURDATE)) dateseq FROM seq_0_to_6

针对从“2010-01-01”开始的一连串为时十年的DATE值，执行以下操作。

SELECT FROM_DAYS(seq + TO_DAYS('2010-01-01')) dateseq
  FROM seq_0_to_3800
 WHERE FROM_DAYS(seq + TO_DAYS('2010-01-01')) < '2010-01-01' + INTERVAL 10 YEAR

如果你还没有使用MariaDB，请考虑一下。

- O. Jones

---

我在这里尝试了：http://sqlzoo.net/wiki/Guest_House_Assessment_Hard，但它没有起作用。然而，很有可能是因为我们在浏览器中使用的是包装过的MariaDB，所以仅仅因为这个原因就无法工作。 - George Pligoropoulos

这是一个惊人的解决方案，它可以让您做很多事情，比如根据每日、每周、每月、每年、双周等重复频率计算预计收入。 - Calin

关于Percona分支中序列生成的好文章以及更多内容：https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/ - rantanplan

Answer 4

尝试这个...它在我的mysql 8.0版本中有效。 您可以根据需要修改以下查询范围

尝试此方法...对我来说在mysql 8.0版本中有效。 您可以根据需要修改以下查询。

WITH recursive numbers AS (
    select 0 as Date
   union all
   select Date + 1
   from numbers
   where Date < 10)
select * from numbers;

是的，不需要像您帖子中提到的那样创建表格。

- Ajay Venkata Raju

---

不错的解决方案，但是它仅适用于小于1000的数字，这是 cte_max_recursion_depth 的默认值。 - saintlyzero

Answer 5

MySQL中没有序列号生成器(CREATE SEQUENCE)。最接近的是AUTO_INCREMENT，它可以帮助你构建表格。

- eed3si9n

---

到目前为止，MySQL 8还不支持这个功能。该功能已在MariaDB和PostgreSQL中实现。 - Mahoor13

有趣的是，我在MySQL 5.7中测试了使用AUTO_INCREMENT构建表格，虽然这似乎应该可以工作，但如果您尝试一次性插入大量条目到表格中，它最终会跳过数字。测试代码。看到这种行为，我感到非常惊讶。我不确定我的测试中做了什么导致了这种情况。 - klhr

Answer 6

1到100000之间的数字序列：

SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a 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) t1,
(select 0 b 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) t2,
(select 0 c 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) t3,
(select 0 d 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) t4,
(select 0 e 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) t5
order by 1

我使用它来审计是否有一些数字顺序不对，就像这样：

select * from (
    select 121 id
    union all select 123
    union all select 125
    union all select 126
    union all select 127
    union all select 128
    union all select 129
) a
right join (
    SELECT e*10000+d*1000+c*100+b*10+a n FROM
    (select 0 a 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) t1,
    (select 0 b 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) t2,
    (select 0 c 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) t3,
    (select 0 d 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) t4,
    (select 0 e 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) t5
    order by 1
) seq on seq.n=a.id
where seq.n between 121 and 129
and   id is null

结果将是序列中121和129之间数字122和124之间的差距：

id     n
----   ---
null   122
null   124

也许能帮到某些人！

- lynx_74

---

我运行了没有“order by 1”的查询，结果生成的数字不是按顺序排列的。我会用“order by n”来编写查询。 “Order by 1”似乎毫无意义，但却可以使查询正确排序整数。为什么它有效？为什么“order by 1”比“order by n”更受欢迎？ - Barzee

Answer 7

有一种方法可以在单个查询中获取一系列值，但速度较慢。可以通过使用缓存表来加速。

假设您想要选择所有布尔值的范围：

SELECT 0 as b UNION SELECT 1 as b;

我们可以创建一个视图。

CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;

那么你可以逐个字节进行操作。

CREATE VIEW ViewByteValues AS
SELECT b0.b + b1.b*2 + b2.b*4 + b3.b*8 + b4.b*16 + b5.b*32 + b6.b*64 + b7.b*128 as v FROM
ViewBoolean b0,ViewBoolean b1,ViewBoolean b2,ViewBoolean b3,ViewBoolean b4,ViewBoolean b5,ViewBoolean b6,ViewBoolean b7;

那么你可以执行a操作。

CREATE VIEW ViewInt16 AS
SELECT b0.v + b1.v*256 as v FROM
ViewByteValues b0,ViewByteValues b1;

那么你可以做这个。

SELECT v+MIN as x FROM ViewInt16 WHERE v<MAX-MIN;

为了加快速度，我跳过了字节值的自动计算，而是自己制作了一个工具。

CREATE VIEW ViewByteValues AS
SELECT 0 as v UNION SELECT 1 as v UNION SELECT ...
...
...254 as v UNION SELECT 255 as v;

如果需要一段日期范围，您可以这样做。

SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE v<NumDays;

或者

SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE day<'end_date';

你可以通过稍微更快的MAKEDATE函数来加速此过程。

SELECT MAKEDATE(start_year,1+v) as day FRON ViewInt16 WHERE day>'start_date' AND day<'end_date';

请注意，这些技巧非常缓慢，仅允许在预定义域中创建有限序列（例如int16 = 0...65536）。

我相信您可以稍微修改查询以加快速度，通过提示MySQL停止计算；（使用ON子句而不是WHERE子句等方法）

例如：

SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0,
ViewByteValues b1,
ViewByteValues b2,
ViewByteValues b3
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < MAX-MIN;

我会将您的SQL服务器保持繁忙几个小时。

然而，

SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0
INNER JOIN ViewByteValues b1 ON (b1.v*256<(MAX-MIN))
INNER JOIN ViewByteValues b2 ON (b2.v*65536<(MAX-MIN))
INNER JOIN ViewByteValues b3 ON (b3.v*16777216<(MAX-MIN)
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < (MAX-MIN);

只要你用LIMIT 1,30或类似的方式限制结果，即使MAX-MIN很大，查询速度也会相对较快。但是，使用COUNT(*)将需要很长时间。如果在MAX-MIN大于100k时添加ORDER BY，计算时间将再次延长数秒...

- CorvusCorax

Answer 8

计数器从1到1000：

• 不需要创建表格
• 执行时间约为0.0014秒
• 可以转换成视图

    select tt.row from
    (
    SELECT cast( concat(t.0,t2.0,t3.0) + 1 As UNSIGNED) as 'row' FROM 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t,
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
    ) tt
    order by tt.row

---

来源： 回答，Seth McCauley在回答下方的评论。

- Max Makhrov

Answer 9

您可以尝试像这样做：

您可以尝试像这样做：

SELECT @rn:=@rn+1 as n
FROM (select @rn:=2)t, `order` rows_1, `order` rows_2 --, rows_n as needed...
LIMIT 4

这里的order只是一个有相当多行的表格的例子。

编辑：原回答是错误的，任何功劳应归于David Poor，他提供了同样概念的工作示例。

- John Nilsson

---

我得想一想，但它看起来很整洁！ - BCS

基本上，最内层的选择将会初始化一个会话变量为2，然后外部选择将会为每一行递增这个变量。在这种情况下，与order进行笛卡尔积意味着该变量将与从2到订单数+2的数字序列相关联（限制4将其限制为4个数字）。 - John Nilsson

这个“解决方案”似乎需要一个现有的表order，其中包含可能匹配的整数的完整列表。如果我尝试不加入到一个已经存在有效整数的大型预先存在的表中运行，我将无法得到除单行结果之外的任何内容。 - rektide

@rektide，它不是“看起来”。它确实是错误的，根本不起作用。 - Pacerier

哦，这是很久以前的事了。我想我的意图是“顺序”，就像采购订单一样（当时我正在做ERP系统，在那种系统中，可以预期这样的表格相当大）。 但是我不认为我的意图是使用该表格中的实际整数。我会看看能否稍微改进一下。 - John Nilsson

Answer 10

最简单的方法是：

SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;

或者在一个查询中：

SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM (SELECT @seq := 0) s, your_table yt;

这里使用 FLOOR() 函数，将 FLOAT 转换为 INTEGER。有时候这是必需的。

我的回答受到了 David Poor回答 的启发。感谢David！

- simhumileco

---

非常有帮助，谢谢！我在 Moodle 的 get_records_sql 中搜索了这个来填充我的结果中的第一个字段，目的是“使第一列成为独一无二的”。效果很好。 - undefined