MySQL数据库时间类型datetime

数据库中可以用datetime、bigint、timestamp来表示时间，那么选择什么类型来存储时间比较合适呢？

前期数据准备

通过程序往数据库插入50w数据

数据表：

CREATETABLE`users`(`id`int(11)NOTNULLAUTO_INCREMENT,`time_date`datetimeNOTNULL,`time_timestamp`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`time_long`bigint(20)NOTNULL,PRIMARYKEY(`id`),KEY`time_long`(`time_long`),KEY`time_timestamp`(`time_timestamp`),KEY`time_date`(`time_date`))ENGINE=InnoDBAUTO_INCREMENT=DEFAULTCHARSET=latin1

其中time_long、time_timestamp、time_date为同一时间的不同存储格式

实体类users

/***

authorhetiantian*

date/10/21**/

Builder

DatapublicclassUsers{/***自增唯一id**/privateLongid;/***date类型的时间**/privateDatetimeDate;/***timestamp类型的时间**/privateTimestamptimeTimestamp;/***long类型的时间**/privatelongtimeLong;}

dao层接口

/***

authorhetiantian*

date/10/21**/

MapperpublicinterfaceUsersMapper{

Insert("insertintousers(time_date,time_timestamp,time_long)value(#{timeDate},#{timeTimestamp},#{timeLong})")

Options(useGeneratedKeys=true,keyProperty="id",keyColumn="id")intsaveUsers(Usersusers);}

测试类往数据库插入数据

publicclassUsersMapperTestextendsBaseTest{

ResourceprivateUsersMapperusersMapper;

Testpublicvoidtest(){for(inti=0;i;i++){longtime=System.currentTimeMillis();usersMapper.saveUsers(Users.builder().timeDate(newDate(time)).timeLong(time).timeTimestamp(newTimestamp(time)).build());}}}

生成数据代码方至github：如果不想用代码生成，而是想通过sql文件导入数据，

SQL查询速率测试

通过datetime类型查询：

selectcount(*)fromuserswheretime_date="-10-:32:44"andtime_date="-10-:41:22"

耗时：0.

通过timestamp类型查询

selectcount(*)fromuserswheretime_timestamp="-10-:32:44"andtime_timestamp="-10-:41:22"

耗时：0.

通过bigint类型查询

selectcount(*)fromuserswheretime_long=1andtime_long=2

耗时：0.s

结论在InnoDB存储引擎下，通过时间范围查找，性能bigintdatetimetimestamp

SQL分组速率测试

使用bigint进行分组会每条数据进行一个分组，如果将bigint做一个转化在去分组就没有比较的意义了，转化也是需要时间的

通过datetime类型分组：

selecttime_date,count(*)fromusersgroupbytime_date

耗时：0.s

通过timestamp类型分组：

selecttime_timestamp,count(*)fromusersgroupbytime_timestamp

耗时：0.s

结论在InnoDB存储引擎下，通过时间分组，性能timestampdatetime，但是相差不大

SQL排序速率测试

通过datetime类型排序：

select*fromusersorderbytime_date

耗时：1.s

通过timestamp类型排序

select*fromusersorderbytime_timestamp

耗时：0.s

通过bigint类型排序

select*fromusersorderbytime_long

耗时：0.s

结论在InnoDB存储引擎下，通过时间排序，性能biginttimestampdatetime

小结

如果需要对时间字段进行操作(如通过时间范围查找或者排序等)，推荐使用bigint，如果时间字段不需要进行任何操作，推荐使用timestamp，使用4个字节保存比较节省空间，但是只能记录到2年记录的时间有限

转载请注明：http://www.aierlanlan.com/cyrz/2175.html