在 mySQL 中生成一个整数数列

我需要使用一个包含整数 n到 m的表/result-set/进行连接。有没有一种简单的方法可以不用构建表就能得到它呢？

(顺便问一下，这种类型的结构叫什么，“ 元查询”?)

m-n的界限是合理的(< 1000)

170856 次浏览
小开
最佳答案

在 MySQL 中没有序列号生成器(创建序列) ，最接近的是 AUTO_INCREMENT，它可以帮助您构造表。

小开

M 有多大？

你可以这样做:

create table two select null foo union all select null;
create temporary table seq ( foo int primary key auto_increment ) auto_increment=9 select a.foo from two a, two b, two c, two d;
select * from seq where foo <= 23;

其中 auto _ Increment 设置为 n，where 子句与 m 相比较，两个表的重复次数至少是 ceil (log (m-n + 1)/log (2))。

(可以通过在创建临时表序列中用(选择 null foo union all select null)替换 two 来省略非临时 two 表。)

小开

您似乎能够用以下方法构造相当大的集合:

select 9 colname union all select 10 union all select 11 union all select 12 union all select 13 ...

我在5300年发现一个解析器堆栈溢出，在5.0.51 a。

小开

你可以试试这样:

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

其中 order只是一些具有相当大的行集的表的一个例子。

编辑: 最初的答案是错误的，任何功劳都应该归功于大卫 · 普尔，他提供了一个同样概念的工作实例

小开

如果你使用的是 Oracle，那么“流水线函数”就是最好的选择，不幸的是，MySQL 没有这样的结构。

根据您想要的数字集的大小，我看到了两种简单的方法: 要么为单个查询填充一个只有您需要的数字的临时表(可能使用存储过程填充的内存表) ，要么预先构建一个从1到1,000,000计数的大表并选择它的有界区域。

小开

警告: 如果一次插入一行数字，最终将执行 N 个命令，其中 N 是需要插入的行数。

您可以通过使用一个临时表(参见下面插入从10000到10699的数字)将其降到 O (log N) :

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`));
Query OK, 0 rows affected (0.11 sec)


mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`;
Query OK, 16 rows affected (0.03 sec)
Records: 16  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`;
Query OK, 32 rows affected (0.03 sec)
Records: 32  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`;
Query OK, 64 rows affected (0.03 sec)
Records: 64  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`;
Query OK, 128 rows affected (0.05 sec)
Records: 128  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`;
Query OK, 256 rows affected (0.03 sec)
Records: 256  Duplicates: 0  Warnings: 0


mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`;
Query OK, 512 rows affected (0.11 sec)
Records: 512  Duplicates: 0  Warnings: 0


mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700;
Query OK, 700 rows affected (0.16 sec)
Records: 700  Duplicates: 0  Warnings: 0

编辑: 仅供参考，不幸的是，这不适用于真正的 使用 MySQL 5.0的临时表，因为它不能插入到自己(您可以在两个临时表之间来回跳转)。

编辑: 您可以使用 内存存储引擎来防止这实际上成为“真实”数据库的一个流失。我想知道是否有人开发了一个“ NUMBERS”虚拟存储引擎来实例化虚拟存储以创建这样的序列。(唉，在 MySQL 之外是不可移植的)

小开

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

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

单个查询，快速，并完全做我想要的: 现在我可以“编号”从一个复杂的查询中找到的“选择”，唯一的数字从1开始，并为结果中的每一行递增一次。

我认为这也适用于上面列出的问题: 调整 @row的初始起始值，并添加一个限制条款来设置最大值。

顺便说一下: 我认为“ r”并不是真正需要的。

谢谢

小开

下面将返回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;
小开

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

假设您想要一个包含所有布尔值范围的 select:

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;

然后你可以做一个

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';

请注意，这些技巧非常缓慢，只允许在预定义的域中创建 FINITE 序列(例如 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);

将运行相当快-即使 MAX-MIN 是巨大的，只要你限制与极限1,30或东西的结果。然而，一个计数(*)将需要很长时间，如果您在 MAX-MIN 大于100k 时错误地添加了 ORDER BY，那么将再次需要几秒钟来计算..。

小开

下面是其他答案中使用的技巧的简洁二进制版本:

select ((((((b7.0 << 1 | b6.0) << 1 | b5.0) << 1 | b4.0)
<< 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
from (select 0 union all select 1) as b0,
(select 0 union all select 1) as b1,
(select 0 union all select 1) as b2,
(select 0 union all select 1) as b3,
(select 0 union all select 1) as b4,
(select 0 union all select 1) as b5,
(select 0 union all select 1) as b6,
(select 0 union all select 1) as b7

没有唯一或排序阶段，没有字符串到数字的转换，没有算术操作，并且每个虚表只有2行，所以应该非常快。

这个版本使用8“位”，所以它从0到255计数，但你可以很容易地调整。

小开

1到100.000之间的数字序列:

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

也许能帮到别人！

小开

如果您碰巧使用的是 MySQL 的 MariaDB 分支，SEQUENCE引擎允许直接生成数字序列。它通过使用虚拟(假)一列表来实现这一点。

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

     SELECT seq FROM seq_1_to_1000;

从0到11，这样做。

     SELECT seq FROM seq_0_to_11;

对于从今天开始连续一周的 DATE 值，执行以下操作。

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，请考虑一下。

小开

这个查询生成从0到1023的数字。我相信它可以在任何 sql 数据库中工作:

select
i0.i
+i1.i*2
+i2.i*4
+i3.i*8
+i4.i*16
+i5.i*32
+i6.i*64
+i7.i*128
+i8.i*256
+i9.i*512
as i
from
(select 0 as i union select 1) as i0
cross join (select 0 as i union select 1) as i1
cross join (select 0 as i union select 1) as i2
cross join (select 0 as i union select 1) as i3
cross join (select 0 as i union select 1) as i4
cross join (select 0 as i union select 1) as i5
cross join (select 0 as i union select 1) as i6
cross join (select 0 as i union select 1) as i7
cross join (select 0 as i union select 1) as i8
cross join (select 0 as i union select 1) as i9
小开

最简单的方法是:

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()函数来获得 INTEGER代替 FLOAT。有时需要使用它。

我的答案是受到 大卫 · 普尔的回答的启发。谢谢大卫！

小开

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

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

是的，不需要像文章中提到的那样创建一个表格

小开

从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

出处: 回答，赛斯麦考利评论下面的答案。

小开 
with t1 as (
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
)
SELECT ROW_NUMBER() over ()
FROM
t1,
t1 as t2;

可以继续对 t1表进行别名，不管这个表有多大(10的 n 次方)。然后可以添加一个极限 X 来切断它。

小开

从 Mariadb 10.2开始(MySQL 现在也有了) ，您可以使用公共表表达式(Common Table Expression，CTE)非常优雅地完成这项工作。

开始了:

with recursive numbers (n) as (
select 1 as n
union
select n+1 from numbers where n<100
)
select * from numbers

当然，您现在可以将这个 numbers CTE 与您自己的表连接起来，这样就得到了1。.N (或 n。M).


提交答案