ParoX - 1 year ago 115

SQL Question

I am trying to get a sequential number table from 1 to 20 million. (or 0 to 20 million)

I am rather awestruck at how difficult it's been to get a MySQL-compatible solution to this common problem.

Similar to this: Creating a "Numbers Table" in mysql

But the the answer only goes to 1 million. I am not really understanding the bit shift calculations.

I've seen many SQL answers but most are for databases that aren't MySQL, so I can't adopt the code due to lack of knowledge of both MySQL and the other.

Some references:

SQL, Auxiliary table of numbers

What is the best way to create and populate a numbers table?

Please make sure the code you post is compatible in MySQL and is semicolon delimited so I can run it in PhpMyAdmin. I'd appreciate the table to be named

`numbers`

`i`

I will benchmark each solution, so the it's archived and hopefully will show up for the next time someone tries to search for this problem.

Benchmarks thus far:

Times are in seconds.

`+---------------+------------------+---------+-----------+------------+`

| Author | Method | 10,000 | 1,000,000 | 20,000,000 |

+---------------+------------------+---------+-----------+------------+

| Devon Bernard | PHP Many Queries | 0.38847 | 39.32716 | ~ 786.54 |

| Bhare | PHP Few Queries | 0.00831 | 0.94738 | 19.58823 |

| psadac,Bhare | LOAD DATA | 0.00549 | 0.43855 | 10.55236 |

| kjtl | Bitwise | 1.36076 | 1.48300 | 4.79226 |

+---------------+------------------+---------+-----------+------------+

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

```
-- To use the bitwise solution you need a view of 2 to the power 25.
-- the following solution is derived from http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql
-- the following solution ran in 43.8 seconds with the primary key, without it 4.56 seconds.
-- create a view that has 2 to the power 25 minus 1
-- 2 ^ 1
CREATE or replace VIEW `two_to_the_power_01_minus_1` AS select 0 AS `n` union all select 1 AS `1`;
-- 2 ^ 2
CREATE or replace VIEW `two_to_the_power_02_minus_1`
AS select
((`hi`.`n` << 1) | `lo`.`n`) AS `n`
from (`two_to_the_power_01_minus_1` `lo` join `two_to_the_power_01_minus_1` `hi`) ;
-- 2 ^ 4
CREATE or replace VIEW `two_to_the_power_04_minus_1`
AS select
((`hi`.`n` << 2 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_02_minus_1` `lo` join `two_to_the_power_02_minus_1` `hi`) ;
-- 2 ^ 8
CREATE or replace VIEW `two_to_the_power_08_minus_1`
AS select
((`hi`.`n` << 4 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_04_minus_1` `lo` join `two_to_the_power_04_minus_1` `hi`) ;
-- 2 ^ 12
CREATE or replace VIEW `two_to_the_power_12_minus_1`
AS select
((`hi`.`n` << 8 ) | `lo`.`n`) AS `n`
from (`two_to_the_power_08_minus_1` `lo` join `two_to_the_power_04_minus_1` `hi`) ;
-- 2 ^ 13
CREATE or replace VIEW `two_to_the_power_13_minus_1`
AS select
((`hi`.`n` << 1) | `lo`.`n`) AS `n`
from (`two_to_the_power_01_minus_1` `lo` join `two_to_the_power_12_minus_1` `hi`);
-- create a table to store the interim results for speed of retrieval
drop table if exists numbers_2_to_the_power_13_minus_1;
create table `numbers_2_to_the_power_13_minus_1` (
`i` int(11) unsigned
) ENGINE=myisam DEFAULT CHARSET=latin1 ;
-- faster 2 ^ 13
insert into numbers_2_to_the_power_13_minus_1( i )
select n from `two_to_the_power_13_minus_1` ;
-- faster 2 ^ 12
CREATE or replace view `numbers_2_to_the_power_12_minus_1`
AS select
`numbers_2_to_the_power_13_minus_1`.`i` AS `i`
from `numbers_2_to_the_power_13_minus_1`
where (`numbers_2_to_the_power_13_minus_1`.`i` < (1 << 12));
-- faster 2 ^ 25
CREATE or replace VIEW `numbers_2_to_the_power_25_minus_1`
AS select
((`hi`.`i` << 12) | `lo`.`i`) AS `i`
from (`numbers_2_to_the_power_12_minus_1` `lo` join `numbers_2_to_the_power_13_minus_1` `hi`);
-- create table for results
drop table if exists numbers ;
create table `numbers` (
`i` int(11) signed
, primary key(`i`)
) ENGINE=myisam DEFAULT CHARSET=latin1;
-- insert the numbers
insert into numbers(i)
select i from numbers_2_to_the_power_25_minus_1
where i <= 20000000 ;
drop view if exists numbers_2_to_the_power_25_minus_1 ;
drop view if exists numbers_2_to_the_power_12_minus_1 ;
drop table if exists numbers_2_to_the_power_13_minus_1 ;
drop view if exists two_to_the_power_13_minus_1 ;
drop view if exists two_to_the_power_12_minus_1 ;
drop view if exists two_to_the_power_08_minus_1 ;
drop view if exists two_to_the_power_04_minus_1 ;
drop view if exists two_to_the_power_02_minus_1 ;
drop view if exists two_to_the_power_01_minus_1 ;
```

Recommended from our users: **Dynamic Network Monitoring from WhatsUp Gold from IPSwitch**. ** Free Download**