在mysql中时间可以使用int varchar datetime,timestamp存储，下面测试下对比看看这几种类型的区别。
生成测试数据
遇到内存表提示table is full的问题，修改了set global max_heap_table_size，需要重建表才生效。
测试表数据都是一致的，int类型的时间也是通过转换成时间戳存放的
mysql> select count(*) from vote_recored_int;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.28 sec)
mysql> select count(*) from vote_recored_varchar;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.30 sec)
mysql> select count(*) from vote_record;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.15 sec)
mysql> show create table vote_record;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vote_record | CREATE TABLE `vote_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL,
`vote_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table vote_recored_int;
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vote_recored_int | CREATE TABLE `vote_recored_int` (
`id` int(11) NOT NULL DEFAULT '0',
`user_id` varchar(20) NOT NULL,
`vote_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`create_time` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table vote_recored_varchar;
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vote_recored_varchar | CREATE TABLE `vote_recored_varchar` (
`id` int(11) NOT NULL DEFAULT '0',
`user_id` varchar(20) NOT NULL,
`vote_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`create_time` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from vote_record where create_time >'2018-12-13 10:07:24' and create_time <'2018-12-13 10:08:06';
+----------+
| count(*) |
+----------+
| 84189 |
+----------+
1 row in set (0.23 sec)
mysql> select count(*) from vote_recored_varchar where create_time >'2018-12-13 10:07:24' and create_time <'2018-12-13 10:08:06';
+----------+
| count(*) |
+----------+
| 84189 |
+----------+
1 row in set (0.48 sec)
mysql> select count(*) from vote_recored_int where create_time >1544666844 and create_time <1544666886;
+----------+
| count(*) |
+----------+
| 84189 |
+----------+
1 row in set (0.30 sec)
测试select count(*) from vote_recored_int where create_time >1544666844 and create_time <1544666886 order by create_time；这种结构的语句
datetime用了0.5 varchar用了0.7，int用了0.51
总体来说最不该设置varchar，效率低，没有校验，其次int效率也不如datetime高，虽然磁盘空间能少4字节，但是效率和数据校验上都没有datetime好，没有理由使用int放时间类型的值。
