在MySQL中生成一个整数序列。-generateanintegersequenceinMySQL

2  

How big is m?

米有多大?

You could do something like:

你可以这样做:

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;

where the auto_increment is set to n and the where clause compares to m and the number of times the two table is repeated is at least ceil(log(m-n+1)/log(2)).

其中auto_increment设置为n, where子句比较m，且两个表重复的次数至少为ceil(log(m-n+1)/log(2))。

(The non-temporary two table could be omitted by replacing two with (select null foo union all select null) in the create temporary table seq.)

(在创建临时表seq中，替换两个with(选择null foo union all select null)可以省略非临时的两个表。)

2  

Sequence of numbers between 1 and 100.000:

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

I use it to audit if some number is out of sequence, something like this:

我用它来审计如果某个数字不符合顺序，像这样:

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

The result will be the gap of number 122 and 124 of sequence between 121 and 129:

结果将是第122和第124号序列在121和129之间的间隙:

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

Maybe it helps someone!

也许它能帮助一些人!

1  

You appear to be able to construct reasonably large sets with:

你似乎能够构造相当大的集合，包括:

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

I got a parser stack overflow in the 5300's, on 5.0.51a.

我得到了一个解析器堆栈溢出在5300，在5.0.51a。

1  

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

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

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

您可以通过使用临时表(参见下面关于从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

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).

编辑:fyi，不幸的是，这不能使用一个真正的临时表，因为它不能插入到它本身(您可以在两个临时表之间来回跳转)。

edit: You could use a MEMORY storage engine to prevent this from actually being a drain on the "real" database. I wonder if someone has developed a "NUMBERS" virtual storage engine to instantiate virtual storage to create sequences such as this. (alas, nonportable outside MySQL)

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

1  

Here is a compact binary version of the technique used in other answers here:

这里是一个紧凑的二进制版本的技术，用于其他的答案:

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

There are no unique or sorting phases, no string to number conversion, no arithmetic operations, and each dummy table only has 2 rows, so it should be pretty fast.

没有唯一的或排序的阶段，没有字符串到数字的转换，没有算术运算，每个虚拟表只有2行，所以它应该很快。

This version uses 8 "bits" so it counts from 0 to 255, but you can easily tweak that.

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

0  

If you were using Oracle, 'pipelined functions' would be the way to go. Unfortunately, MySQL has no such construct.

如果您正在使用Oracle，“管道函数”将是一种方法。不幸的是，MySQL没有这样的结构。

Depending on the the scale of the numbers you want sets of, I see two simple ways to go : you either populate a temporary table with just the numbers you need (possibly using memory tables populated by a stored procedure) for a single query or, up front, you build a big table that counts from 1 to 1,000,000 and select bounded regions of it.

根据你想要的数字集的规模,我看到两个简单的方法:你要么填充一个临时表只有你需要的数字(可能使用内存表的存储过程)为一个查询,或者,你构建一个大表,计算从1到1000000,并选择有界区域。

0  

This query generates numbers from 0 to 1023. I believe it would work in any sql database flavor:

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