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)
This question is related to
mysql
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
Counter from 1 to 1000:
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.
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)
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
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.)
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;
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
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.)
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
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.
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.)
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
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.
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
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.
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;
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.
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.
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)
SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;
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!
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!
Counter from 1 to 1000:
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.
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
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.
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
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
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.
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...
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
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.
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...
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.
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)
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!
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.)
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.
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.
SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;
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!
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)
Source: Stackoverflow.com