Generate an integer sequence in MySQL

Question

I need to do a join with a table/result-set/whatever that has the integers n to m inclusive. Is there a trivial way to get that without just building the table?

(BTW what would that type of construct be called, a "Meta query"?)

m-n is bounded to something reasonable ( < 1000's)

Answer 1

try with following

select
ROW_NUMBER() OVER (ORDER BY id asc) AS 'rowId', otherField from table;

Answer 2

As of Mariadb 10.2 (and MySQL has it too now), you can do this very elegantly with a Common Table Expression (CTE).

Here we go:

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

Of course, you can now join this numbers CTE with your own tables, and you've got your 1..n (or n..m by the same approach).

Answer 3

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

select 9 colname 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.

Answer 4

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;

Can continue aliasing t1 table for however large of a table you want (10 to the nth power). And then can add a limit X to cut it off.

Answer 5

Counter from 1 to 1000:

• no need to create a table
• time to execute ~ 0.0014 sec
• can be converted into a view

    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

---

Credits: answer, comment by Seth McCauley below the answer.

Answer 6

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

Depending on 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.

Answer 7

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.

I think this will also work for the issue listed above: adjust the initial starting value for @row and add a limit clause to set the maximum.

BTW: I think that the "r" is not really needed.

ddsp

Answer 8

The following will return 1..10000 and is not so slow

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;

Answer 9

try this.. it works for me in mysql version 8.0. you can modify below query according to your required range

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

Answer 10

The simplest way to do this is:

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

or in one query:

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

The FLOOR() function is used here to get an INTEGER in place of a FLOAT. Sometimes it is needed.

My answer was inspired by David Poor answer. Thanks David!

Answer 11

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

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

Answer 12

If you happen to be using the MariaDB fork of MySQL, the SEQUENCE engine allows direct generation of number sequences. It does this by using virtual (fake) one column tables.

For example, to generate the sequence of integers from 1 to 1000, do this

     SELECT seq FROM seq_1_to_1000;

For 0 to 11, do this.

     SELECT seq FROM seq_0_to_11;

For a week's worth of consecutive DATE values starting today, do this.

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

For a decade's worth of consecutive DATE values starting with '2010-01-01' do this.

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.

Answer 13

Sequence of numbers between 1 and 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:

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

Maybe it helps someone!

Answer 14

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.

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

Answer 15

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 order is just en example of some table with a reasonably large set of rows.

Edit: The original answer was wrong, and any credit should go to David Poor who provided a working example of the same concept

Answer 16

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:

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

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 )

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)

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

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...

Answer 17

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.

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

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).

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)

Answer 18

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)).

(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.)

Answer 19

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.