logo标签列表

在MySQL中生成整数序列

mysql
82

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

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

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

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

您似乎可以使用以下方法构建较大的集合：

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

我在5.0.51a版本中遇到了5300的解析器堆栈溢出错误。

7
哇，真的做测试了？哇！ - BCS
4
@BCS，请在浏览器地址栏输入以下内容进行访问：data:text/html,<script>for(var x=1;x<=5678;++x)document.write((x===1?'':' union all ')+'select '+x);</script>。 - Pacerier
1
@Pacerier 很棒的技巧，不仅适用于这个应用程序。 - collapsar
3
这是一种紧凑的二进制技术，与其他答案中使用的技术相同：
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，但你可以轻松调整它。

3

这个查询生成从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
2

从Mariadb 10.2开始（MySQL现在也有了），您可以使用公共表达式（CTE）非常优雅地完成此操作。

下面是具体步骤：

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

当然，现在您可以将此numbers公用表与您自己的表连接起来，从而实现1..n（或使用同样的方法实现n..m）。
2

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;

在自增字段设置为n，where子句与m进行比较，并且重复使用两个表的次数至少为ceil(log(m-n+1)/log(2))。

（通过将two替换为(select null foo union all select null)来创建临时表seq可省略非临时两个表。）

我有点喜欢这个概念，但如果我必须建立表格，我会自动递增并手动添加行直到足够大。 - BCS
1
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来截断它。
1

请尝试以下内容

select
ROW_NUMBER() OVER (ORDER BY id asc) AS 'rowId', otherField from table;
这并没有回答问题。一旦您拥有足够的 声望，您就可以在任何帖子上发表评论;相反，提供不需要询问者澄清的答案。- 来自审核 - SelVazi
1

警告：如果您逐行插入数字，最终将执行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临时表中使用，因为它无法插入自身（您可以在两个临时表之间来回跳转）。

编辑：您可以使用MEMORY存储引擎来防止这实际上对“真实”数据库造成负担。我想知道是否有人开发了一个“NUMBERS”虚拟存储引擎来实例化虚拟存储以创建这样的序列。（遗憾的是，在MySQL之外不可移植）

一个好的KISS方案。另一方面，它确实实现了整个事情；（+1）。 - BCS
是的，很遗憾没有一个SELECT number FROM INTEGERS WHERE number > 0 AND number < 10000……但从技术上讲，我想有人可能会想出一个可以实现这个功能的虚拟存储引擎。 - Jason S
1
嗯......有人踩了这个答案，有什么特别的原因吗？ - Jason S
1
...而且花了某人5年时间才弄清楚原因？ - Jason S
0

如果您使用的是Oracle，那么“管道函数”将是最佳选择。不幸的是，MySQL没有这样的结构。

根据您需要的数字规模，我看到有两种简单的方法：您可以为单个查询使用存储过程填充仅包含所需数字的临时表（可能使用内存表），或者您可以预先构建一个从1计数到1,000,000的大表，并选择其有界区域。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接