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    lt  1000 s

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  CREATE TABLE  tmp keys    k  INTEGER UNSIGNED  PRIMARY KEY   k     Query OK  0 rows affected  0 11 sec   mysql gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  INSERT INTO inttable SELECT k 10000 FROM  tmp keys  WHERE k lt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt 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 lt NumDays    or  SELECT DATE ADD  start date  v  as day FROM ViewInt16 WHERE day lt  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 gt  start date  AND day lt  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   lt  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 lt  MAX-MIN   INNER JOIN ViewByteValues b2 ON  b2 v 65536 lt  MAX-MIN   INNER JOIN ViewByteValues b3 ON  b3 v 16777216 lt  MAX-MIN  WHERE  b0 v   b1 v 256   b2 v 65536   b3 v 16777216   lt   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

User · Answer

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

User · Answer

Here is a compact binary version of the technique used in other answers here   select       b7 0  lt  lt  1   b6 0   lt  lt  1   b5 0   lt  lt  1   b4 0                      lt  lt  1   b3 0   lt  lt  1   b2 0   lt  lt  1   b1 0   lt  lt  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

User · Answer

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

User · Answer

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     lt   2010-01-01    INTERVAL 10 YEAR   If you don t happen to be using MariaDB  please consider it

User · Answer

There is no sequence number generator  CREATE SEQUENCE  in MySQL  Closest thing is AUTO INCREMENT  which can help you construct the table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     lt   2010-01-01    INTERVAL 10 YEAR   If you don t happen to be using MariaDB  please consider it

User · Answer

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

User · Answer

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

User · Answer

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  lt  10  select   from numbers    and yes without creating a table as mentioned in your post

User · Answer

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 lt 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 lt NumDays    or  SELECT DATE ADD  start date  v  as day FROM ViewInt16 WHERE day lt  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 gt  start date  AND day lt  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   lt  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 lt  MAX-MIN   INNER JOIN ViewByteValues b2 ON  b2 v 65536 lt  MAX-MIN   INNER JOIN ViewByteValues b3 ON  b3 v 16777216 lt  MAX-MIN  WHERE  b0 v   b1 v 256   b2 v 65536   b3 v 16777216   lt   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

User · Answer

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

User · Answer

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

User · Answer

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 gt  CREATE TABLE  tmp keys    k  INTEGER UNSIGNED  PRIMARY KEY   k     Query OK  0 rows affected  0 11 sec   mysql gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  INSERT INTO inttable SELECT k 10000 FROM  tmp keys  WHERE k lt 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

User · Answer

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

User · Answer

There is no sequence number generator  CREATE SEQUENCE  in MySQL  Closest thing is AUTO INCREMENT  which can help you construct the table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  10  select   from numbers    and yes without creating a table as mentioned in your post

User · Answer

There is no sequence number generator  CREATE SEQUENCE  in MySQL  Closest thing is AUTO INCREMENT  which can help you construct the table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

There is no sequence number generator  CREATE SEQUENCE  in MySQL  Closest thing is AUTO INCREMENT  which can help you construct the table

User · Answer

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 gt  CREATE TABLE  tmp keys    k  INTEGER UNSIGNED  PRIMARY KEY   k     Query OK  0 rows affected  0 11 sec   mysql gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  INSERT INTO inttable SELECT k 10000 FROM  tmp keys  WHERE k lt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 gt  CREATE TABLE  tmp keys    k  INTEGER UNSIGNED  PRIMARY KEY   k     Query OK  0 rows affected  0 11 sec   mysql gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  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 gt  INSERT INTO inttable SELECT k 10000 FROM  tmp keys  WHERE k lt 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

User · Answer

Here is a compact binary version of the technique used in other answers here   select       b7 0  lt  lt  1   b6 0   lt  lt  1   b5 0   lt  lt  1   b4 0                      lt  lt  1   b3 0   lt  lt  1   b2 0   lt  lt  1   b1 0   lt  lt  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

User · Answer

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

[mysql] Generate an integer sequence in MySQL

Examples related to mysql