mysql中timestamp,datetime,int类型的区别与优劣

前置介绍：

字节

1字节=8位 bit

1字节从00000000到11111111只能表示256个字符，2字节可以表示65535个字符。

int（时间戳）
1. 占用4个字节
2. 建立索引之后，查询速度快
3. 条件范围搜索可以使用使用between
4. 不能使用mysql提供的时间函数
结论：适合需要进行大量时间范围查询的数据表

datetime
1. 占用8个字节

2. 允许为空值，可以自定义值，系统不会自动修改其值。

3. 与时区无关

4. 可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

结论：datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。

 

timestamp
1. 占用4个字节

2. 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
3. TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如'1968-01-01'，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。

4.值以UTC格式保存（ it stores the number of milliseconds）

5.时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

6. 默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。

7. 数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。

8. 默认情况下以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。

9. 如果需要可以设置timestamp不自动更新。通过设置DEFAULT CURRENT_TIMESTAMP 可以实现。

修改自动更新：
`field_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
修改不自动更新
`field_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP 

 

结论：timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。（如果需要可以设置timestamp不自动更新）

首先 DATETIM和TIMESTAMP类型所占的存储空间不同，前者8个字节，后者4个字节，这样造成的后果是两者能表示的时间范围不同。前者范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59，后者范围为1970-01-01 08:00:01到2038-01-19 11:14:07。所以可以看到TIMESTAMP支持的范围比DATATIME要小,容易出现超出的情况.

其次，TIMESTAMP类型在默认情况下，insert、update 数据时，TIMESTAMP列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。

第三，TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响

所以一般来说，我比较倾向选择DATETIME，至于你说到索引的问题，选择DATETIME作为索引，如果碰到大量数据查询慢的情况，也可以分区表解决。