在 MySQL 中生成整数序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/304461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate an integer sequence in MySQL
提问by BCS
I need to do a join with a table/result-set/whatever that has the integers nto minclusive. Is there a trivial way to get that without just building the table?
我需要与表/结果集/任何包含整数的内容n进行连接m。有没有一种简单的方法可以在不构建表格的情况下获得它？
(BTW what would that type of construct be called, a "Meta query"?)
（顺便说一句，这种类型的构造会被称为什么，“元查询”？）
m-nis bounded to something reasonable ( < 1000's)
m-n被限制在合理的范围内（< 1000's）
采纳答案by Eugene Yokota
There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.
MySQL 中没有序列号生成器 ( CREATE SEQUENCE)。最接近的是AUTO_INCREMENT，它可以帮助您构建表格。
回答by David Poor
I found this solution on the web
我在网上找到了这个解决方案
SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r
Single query, fast, and does exactly what I wanted: now I can "number" the "selections" found from a complex query with unique numbers starting at 1 and incrementing once for each row in the result.
单个查询，快速，并且完全符合我的要求：现在我可以“编号”从复杂查询中找到的“选择”，其中唯一编号从 1 开始，并为结果中的每一行递增一次。
I think this will also work for the issue listed above: adjust the initial starting value for @rowand add a limit clause to set the maximum.
我认为这也适用于上面列出的问题：调整初始起始值@row并添加限制子句以设置最大值。
BTW: I think that the "r" is not really needed.
顺便说一句：我认为“r”并不是真正需要的。
ddsp
滴滴
回答by Unreason
The following will return 1..10000 and is not so slow
以下将返回 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;
回答by O. Jones
If you happen to be using the MariaDB fork of MySQL, the SEQUENCEengineallows direct generation of number sequences. It does this by using virtual (fake) one column tables.
如果您碰巧使用MySQL的MariaDB 分支，该SEQUENCE引擎允许直接生成数字序列。它通过使用虚拟（假）一列表来做到这一点。
For example, to generate the sequence of integers from 1 to 1000, do this
例如，要生成从 1 到 1000 的整数序列，请执行以下操作
SELECT seq FROM seq_1_to_1000;
For 0 to 11, do this.
对于 0 到 11，执行此操作。
SELECT seq FROM seq_0_to_11;
For a week's worth of consecutive DATE values starting today, do this.
对于从今天开始的一周的连续 DATE 值，请执行此操作。
SELECT FROM_DAYS(seq + TO_DAYS(CURDATE)) dateseq FROM seq_0_to_6
For a decade's worth of consecutive DATEvalues starting with '2010-01-01' do this.
对于DATE以“2010-01-01”开头的十年连续值，请执行此操作。
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
If you don't happen to be using MariaDB, please consider it.
如果您没有使用 MariaDB，请考虑一下。
回答by lynx_74
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:
结果将是 121 和 129 之间序列的编号 122 和 124 的间隙：
id n
---- ---
null 122
null 124
Maybe it helps someone!
也许它可以帮助某人！
回答by John Nilsson
You could try something like this:
你可以尝试这样的事情：
SELECT @rn:=@rn+1 as n
FROM (select @rn:=2)t, `order` rows_1, `order` rows_2 --, rows_n as needed...
LIMIT 4
Where orderis just en example of some table with a reasonably large set of rows.
Whereorder只是具有相当大的行集的某个表的示例。
Edit: The original answer was wrong, and any credit should go to David Poor who provided a working example of the same concept
编辑：最初的答案是错误的，任何功劳都应该归功于提供相同概念的工作示例的大卫·普尔
回答by CorvusCorax
There is a way to get a range of values in a single query, but its a bit slow. It can be sped up by using cache tables.
有一种方法可以在单个查询中获取一系列值，但速度有点慢。它可以通过使用缓存表来加速。
assume you want a select with a range of all BOOLEAN values:
假设您想要一个包含所有 BOOLEAN 值范围的选择：
SELECT 0 as b UNION SELECT 1 as b;
we can make a view
我们可以看看
CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;
then you can do a Byte by
然后你可以做一个字节
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;
then you can do a
那么你可以做一个
CREATE VIEW ViewInt16 AS
SELECT b0.v + b1.v*256 as v FROM
ViewByteValues b0,ViewByteValues b1;
then you can do a
那么你可以做一个
SELECT v+MIN as x FROM ViewInt16 WHERE v<MAX-MIN;
To speed this up I skipped the auto-calculation of byte values and made myself a
为了加快速度，我跳过了字节值的自动计算，并让自己成为
CREATE VIEW ViewByteValues AS
SELECT 0 as v UNION SELECT 1 as v UNION SELECT ...
...
...254 as v UNION SELECT 255 as v;
If you need a range of dates you can do.
如果你需要一个日期范围，你可以做。
SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE v<NumDays;
or
或者
SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE day<'end_date';
you might be able to speed this up with the slightly faster MAKEDATE function
您可以使用稍微快一点的 MAKEDATE 函数来加快速度
SELECT MAKEDATE(start_year,1+v) as day FRON ViewInt16 WHERE day>'start_date' AND day<'end_date';
Please note that this tricks are VERY SLOW and only allow the creation of FINITE sequences in a pre-defined domain (for example int16 = 0...65536 )
请注意，这个技巧非常慢，只允许在预定义域中创建有限序列（例如 int16 = 0...65536 ）
I am sure you can modify the queries a bit to speed things up by hinting to MySQL where to stop calculating ;) (using ON clauses instead of WHERE clauses and stuff like that)
我相信您可以通过向 MySQL 提示停止计算的位置来稍微修改查询以加快速度；)（使用 ON 子句而不是 WHERE 子句之类的东西）
For example:
例如：
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;
will keep your SQL server busy for a few hours
将使您的 SQL 服务器忙碌几个小时
However
然而
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);
will run reasonably fast - even if MAX-MIN is huge as long as you limit the result with LIMIT 1,30 or something. a COUNT(*) however will take ages and if you make the mistake of adding ORDER BY when MAX-MIN is bigger than say 100k it will again take several seconds to calculate...
将运行得相当快——即使 MAX-MIN 很大，只要你用 LIMIT 1,30 或其他东西来限制结果。但是， COUNT(*) 需要很长时间，如果您在 MAX-MIN 大于 100k 时错误地添加了 ORDER BY，则将再次需要几秒钟的时间来计算...
回答by ysth
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 上遇到了解析器堆栈溢出。
回答by George Polevoy
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
回答by Ajay
try this.. it works for me in mysql version 8.0. you can modify below query according to your required range
试试这个..它在mysql 8.0版中对我有用。您可以根据您需要的范围修改以下查询
WITH recursive numbers AS (
select 0 as Date
union all
select Date + 1
from numbers
where Date < 10)
select * from numbers;
and yes without creating a table as mentioned in your post
是的，无需创建您帖子中提到的表格
