Generate an integer sequence in MySQL

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)

Solution 1:

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

Solution 2:

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;

Solution 3:

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.

Solution 4:

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