关于mysql 时间类型的测试

在mysql中时间可以使用int varchar datetime,timestamp存储，下面测试下对比看看这几种类型的区别。
生成测试数据，参考了：https://blog.csdn.net/simplexingfupeng/article/details/79474214
遇到内存表提示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放时间类型的值。