Make a table of sequential ints

from the Artful Common Queries page

You need a table of ints from 1 through 100. Here's one way ... 
create or replace view ints as 
  select 0 as x 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
... or ... 
drop table if exists ints; 
create table ints( i tinyint primary key ); 
insert into ints values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
Then your ints from 1 through 100, or 15 through 743, or ... 
select distinct 100*a.i + 10*b.i + c.i as n 
from ints a 
cross join ints b 
cross join ints c
order by n limit 15,729; -- 743-15+1 inclusive
Or you may need a table of 4,000 sequential ints starting at 1 with interval=4. Each term = 1 + a multiple of 4, so ... 
drop table if exists temp;
create table temp
  select iseq from (
    select 1 + (10000*e.i + 1000*d.i + 
                100*c.i + 10*b.i + a.i) as iseq  
  from ints a, ints b, ints c, ints d, ints e 
  order by iseq
) x
where iseq%4 = 1 and iseq < 16000;
Since MySQL 8.0 and MariaDB 10.2, a Common Table Expression does the job elegantly ... 
set @@cte_max_recursion_depth=5000;
drop table if exists t;
create table t
  with recursive cte as (
    select 1 as i
    union all
    select i+1
    from cte
    where i < 4000
  )
  select * from cte;
You need to save the numbers as 4-byte binary strings? 
create table temp 
select 
  lpad( conv(iseq, 10, 16), 4, 0 ) as ibin
from ( 
  select 
    1 + (10000*e.i + 1000*d.i + 
         100*c.i + 10*b.i + a.i) as iseq   
  from ints a, ints b, ints c, ints d, ints e  
  order by iseq 
) x 
where x.iseq%4 = 1 and iseq < 16000;
Such tables are useful for building all kinds of sequences, for example sequential dates, and for replacing iterative loops that would otherwise require procedural code. In the SQL Server world, they're known as "tally tables".

Last updated 16 Mar 2025