首页 / MYSQL / MYSQL:顺序号码表
MYSQL:顺序号码表
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MYSQL:顺序号码表,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4144字,纯文字阅读大概需要6分钟。
内容图文
![MYSQL:顺序号码表](/upload/InfoBanner/zyjiaocheng/910/8843010e839e40238661ef0c0d05532c.jpg)
我试图获得一个1到2千万的序列号表. (或0到2000万)
我很惊讶为这个常见问题找到一个与MySQL兼容的解决方案有多么困难.
与此类似:Creating a “Numbers Table” in mysql
但答案只有100万.我真的不了解位移计算.
我见过很多SQL的答案,但大多数是针对非MySQL的数据库,所以由于缺乏对MySQL和其他的知识,我不能采用这些代码.
一些参考:
SQL, Auxiliary table of numbers
What is the best way to create and populate a numbers table?
请确保您发布的代码在MySQL中兼容并以分号分隔,以便我可以在PhpMyAdmin中运行它.我很欣赏用名为i的列命名数字的表
我将对每个解决方案进行基准测试,以便将其存档并希望在下次有人尝试搜索此问题时显示.
到目前为止的基准:
时间是几秒钟.
+---------------+------------------+---------+-----------+------------+
| 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 https://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:顺序号码表全部内容,希望文章能够帮你解决MYSQL:顺序号码表所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。