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)

you can have troubles with auto_increment when need include parent and childs at same time, i never use it, nextval is simplier. You can see nextval function for mysql code here: http://stackoverflow.com/questions/8058675/error-in-mysql-bigint-variable-declaration-inside-custom-nextval-function — , Nov 11 '11 at 11:02

score 122 · Answer 1 · edited Oct 30 '19 at 20:36

122

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

edited Oct 30 '19 at 20:36

gcochard

11,408
1
26
41

answered May 24 '09 at 16:27

David Poor

1,261
2
8
2

9

yes `r` is needed - Error 1248: every derived table must have its own alias – Unreason Nov 12 '10 at 21:25
8

The number of rows selected comes from `some_table` not r. If some_table has no rows, you get nothing. I used this to generated a set of test data which just needed names and passwords. The sequence of numbers became the names and the password was just ENCRYPT('passwd') so they'd all be the same. To generate rows, I selected from another table, but didn't actually select any of the columns. It just gave me a row for each row in `some_table`, all of them with sequential numbers. – Mnebuerquo Mar 31 '11 at 20:26
6

+Many, I'm a bit in shock - had no idea MySQL could do this. These two separate queries work, for example, for re-sequencing a subset of a table: `SET @row := 0` and `UPDATE foo SET position = (@row := @row + 1) WHERE ORDER BY last_modified ASC` – Izkata Jun 13 '12 at 20:39
Executing this from C# gives the error, "Parameter '@row' must be defined." But I found a solution here: http://stackoverflow.com/questions/958953/is-it-possible-to-use-a-mysql-user-defined-variable-in-a-net-mysqlcommand – EricP Jun 21 '12 at 19:09
@Izkata, David, Is this behavior guaranteed by the specs? For all you know, a future version could give us the same value for all rows and only do the increment after the entire selection. – Pacerier Apr 01 '15 at 08:30

score 38 · Answer 2 · edited Apr 24 '19 at 10:38

38

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;

edited Apr 24 '19 at 10:38

Antti Kissaniemi

18,944
13
54
47

answered Apr 16 '10 at 10:04

Unreason

12,556
2
34
50

16

an explanation would be nice - `t - t4` each contain 10 rows of dummy data, cartesian product of these is 10^4, i.e. 10000 rows of dummy data, and the outer select is just mysql version of rownumber – Aprillion Jun 21 '12 at 15:57
1

@Aprillion, 2^14 will do the same thing. With only 28 values instead of 40. – Pacerier Apr 01 '15 at 12:28
2

You could get rid of the variable declaration by replacing "@row := @row + 1 as row" with "concat(t.0,t2.0,t3.0,t4.0) + 1 as row", then sorting by "row". To get get an accurate result you would also need to change the duplicate "select 6" in each subquery to "select 2" ("2" isn't currently used but "6" is listed twice.). As a bonus, you could then also shorten each "union all" to "union", since there should no longer be any duplicate rows. – Seth McCauley Jul 06 '16 at 21:20
@Seth McCauley: no need to change 6 to 2. the values aren't even used. might as well all be null. only the `@row := @row + 1 as row` is needed to get the incrementing number per row. the unions just multiply to give the number of rows. – skb Oct 12 '16 at 02:21
This initially failed for me with the error "Every derived table must have its own alias". Easily fixed by giving the final `SELECT` an alias - e.g. add `t5` at the very end. – Steve Chambers May 20 '18 at 15:31
I had to change `AS row` to `AS r` to prevent a syntax error. – ijt Sep 25 '22 at 16:25

O. Jones · Answer 3 · 2017-01-08T13:08:24.057

38

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.

edited Jan 08 '17 at 13:08

answered Jan 08 '17 at 12:51

O. Jones

103,626
17
118
172

I tried this here: http://sqlzoo.net/wiki/Guest_House_Assessment_Hard and it didn't work. However it is highly likely that because in-browser we are working with a wrapped MariaDB then this is not working for only this reason – George Pligoropoulos Dec 09 '17 at 22:47
This is an amazing solution that allows you to do many thinks like calculate the projected revenue based on a recurring schedule that runs on a daily, weekly, monthly, yearly, biweekly, etc. frequency. – Calin Oct 03 '19 at 19:42
Nice article about sequence generation in Percona fork and more: https://www.percona.com/blog/2020/07/27/generating-numeric-sequences-in-mysql/ – rantanplan Jan 24 '23 at 06:06

score 20 · Answer 4 · answered Feb 14 '19 at 10:24

20

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

answered Feb 14 '19 at 10:24

Ajay Venkata Raju

1,098
12
22

3

Nice solution, however, this works for numbers less than 1000 which is the default value of `cte_max_recursion_depth` – saintlyzero Apr 24 '21 at 06:17

score 11 · Accepted Answer · answered Nov 20 '08 at 08:33

11

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

answered Nov 20 '08 at 08:33

Eugene Yokota

94,654
45
215
319

Up to now MySQL 8 does not support this feature. It was implemented in MariaDB and PostgreSQL. – Mahoor13 Jan 10 '21 at 06:44
Interestingly, I tested out using AUTO_INCREMENT to construct a table in MySQL 5.7, and while this seems like it _should_ work, it ends up skipping digits if you try to insert a bunch of entries into the table at once. [test code](https://gist.github.com/KelWill/e6ab0d7ba729064ba8aa4583ac0d30e2). I was pretty surprised to see this behavior. I'm not sure what I'm doing in my test that's causing this. – klhr Jul 07 '22 at 19:09

score 8 · Answer 6 · answered Oct 07 '16 at 13:34

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!

I ran the query without "order by 1", and the query produced numbers that were not in sequential order. I would have written the query with "order by n". "Order by 1" seems meaningless and yet causes the query to order the integers correctly. Why does it work? Why is "order by 1" preferred over "order by n"? — Barzee, Jul 29 '22 at 18:49

John Nilsson · Answer 7 · 2015-04-02T14:43:53.423

5

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

edited Apr 02 '15 at 14:43

answered Dec 30 '08 at 17:55

John Nilsson

17,001
8
32
42

I'm going to have to puzzle that one out but it sure looks neat! – BCS Jan 01 '09 at 03:26
Basically the inner most select initializes a session var to 2 then the outer select increment this var for each row, in this case the cartesian join to `order` means the var will get a relation with a sequence of numbers from 2 to the number of orders + 2 (limit 4 restricts this to 4 numbers) – John Nilsson Jan 01 '09 at 20:57
This "solution" appears to require an existing table `order` containing the complete list of possible matching integers. I cannot get anything other than a single row result if I try running without joining to a large pre-existing table with valid integers already in it. – rektide Apr 02 '13 at 20:42
1

@rektide, It does not "appear". It **is** wrong and does not work at all. – Pacerier Apr 01 '15 at 12:38
Oh, this was a long time ago. I think my intention was "order" as in Purchase Order (I was doing ERP-systems at the time, and in that kind of system such a table could be expected to be reasonably large). But no I don't think my intention was to use actual integers from that table. I'll see if I can improve it a bit. – John Nilsson Apr 02 '15 at 14:29

CorvusCorax · Answer 8 · 2011-02-02T10:38:45.280

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

score 5 · Answer 9 · edited Nov 30 '20 at 13:46

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.

ysth · Answer 10 · 2021-06-23T15:43:12.523

4

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.

edited Jun 23 '21 at 15:43

answered Nov 21 '08 at 05:48

ysth

96,171
6
121
214

7

WOW, just WOW! you actually tested that?! Wow! – BCS Nov 21 '08 at 06:02
4

@BCS, Type this in your url bar: `data:text/html,` – Pacerier Apr 01 '15 at 13:04
1

@Pacerier Great technique,not just for this application. – collapsar Feb 08 '18 at 14:21

simhumileco · Answer 11 · 2018-07-19T16:59:33.763

4

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!

edited Jul 19 '18 at 16:59

answered Jul 19 '18 at 10:36

simhumileco

31,877
16
137
115

score 3 · Answer 12 · answered Jul 15 '16 at 11:04

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.

score 3 · Answer 13 · answered Mar 01 '17 at 14:33

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

ysth · Answer 14 · 2008-11-20T09:42:47.337

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

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

edited Nov 20 '08 at 09:42

answered Nov 20 '08 at 09:03

ysth

96,171
6
121
214

I kind of like the concept, but if I have to build the table anyway, I'll just do an auto increment and add rows manually till it's big enough. – BCS Nov 20 '08 at 22:04

Jason S · Answer 15 · 2009-01-01T16:18:28.013

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)

a nice KISS solution. OTOH it does materialize the whole thing ;(. +1 — BCS, Jan 01 '09 at 03:28
Yeah. It's too bad there's not a SELECT number FROM INTEGERS WHERE number > 0 AND number < 10000... but technically I suppose someone could come up with a virtual storage engine that does this. — Jason S, Jan 01 '09 at 16:14
hmmmm... somebody -1'd this answer, any particular reason why? — Jason S, Mar 19 '10 at 13:57

score 1 · Answer 16 · answered Jun 16 '21 at 04:43

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.

score 1 · Answer 17 · answered Sep 27 '22 at 08:04

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

score 1 · Answer 18 · answered Mar 14 '23 at 11:56

1

try with following

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

answered Mar 14 '23 at 11:56

xixi

21
2

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/34032212) – SelVazi Mar 19 '23 at 17:10

score 0 · Answer 19 · edited Aug 11 '20 at 21:44

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.

Generate an integer sequence in MySQL

19 Answers19

The simplest way to do this is:

or in one query:

Linked

Related