MYSQL:顺序号表 [英] MYSQL: Sequential Number Table

问题描述

我正在尝试获取1到2000万之间的序列号表. (或0到2000万)

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

对于这个常见问题，获得与MySQL兼容的解决方案有多么困难，我感到非常震惊.

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

类似于此:创建"Numbers Table"在mysql中

但是答案只有一百万.我不太了解位移计算.

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

我已经看到了很多SQL答案，但是大多数答案都是针对非MySQL的数据库，因此由于缺乏对MySQL和另一个MySQL的了解，我无法采用该代码.

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.

一些参考文献:

SQL，辅助数字表

什么是最好的方法创建并填充数字表?

请确保您发布的代码在MySQL中兼容并且以分号分隔，以便我可以在PhpMyAdmin中运行它.我希望表名为i

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 with the column called 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.

到目前为止的基准:

时间以秒为单位.

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

推荐答案

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

这篇关于MYSQL:顺序号表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！