Home >Database >Mysql Tutorial >mysql timestamp和int存储时间_MySQL

mysql timestamp和int存储时间_MySQL

WBOY
WBOYOriginal
2016-06-01 13:36:541106browse

bitsCN.com

mysql timestamp和int存储时间

 

Sql代码      

show create table 20130107date;  

  

CREATE TABLE `20130107date` (  

  `id` int(11) NOT NULL AUTO_INCREMENT,  

  `c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  

  `c_date_long` int(20) NOT NULL,  

  `idx_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  

  `idx_date_long` int(11) NOT NULL,  

  PRIMARY KEY (`id`),  

  KEY `20130107date_idx_date` (`idx_date`),  

  KEY `20130107date_idx_long` (`idx_date_long`)  

) ENGINE=InnoDB  

 

里面有90w数据,都是随机的时间. 

先看没有索引的全表扫描 

1 : 

Sql代码  

select COUNT(*) from 20130107date  

where c_date BETWEEN DATE('20110101') and DATE('20110102')  

 

这个需要1.54s 

 

2: 

Sql代码  

select COUNT(*) from 20130107date  

where c_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102')  

 

这个是2.3s 

 

但是可以这样搞 

3 : 

Sql代码  

select UNIX_TIMESTAMP('20110101') ,UNIX_TIMESTAMP('20110102');  

 

得到结果1293811200和1293897600 

 

然后 

Sql代码  

select COUNT(*) from 20130107date  

where c_date_long BETWEEN 1293811200 and 1293897600;  

 

发现变成了0.61s 

1和2的差距还可以说是比较int和比较timestamp的差距,那么2和3的差距呢?难道多出来的时间是每一条记录都要evaluate UNIX_TIMESTAMP('20110102')? 

 

然后用索引 

Sql代码  

select COUNT(*) from 20130107date  

where idx_date_long BETWEEN UNIX_TIMESTAMP('20110101') and UNIX_TIMESTAMP('20110102');  

  

select COUNT(*) from 20130107date  

where idx_date BETWEEN '20110101' and '20110102'  

 

毫无悬念,两个基本都是瞬时的. 

 

bitsCN.com
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn