mysql索引表 MySQL 时间类型用 datetime

因为此前的系统设计都是基于 32 位实现的，我们上面提到过，最多无非是 2 的 31次方 - 1，每个数代表一秒的话，最多表示 68 年。所以 Unix 选取了 1970年1月1日作为UNIX TIME的纪元时间(开始时间)。

这里我们主要还是关心 DATETIME 以及 TIMESTAMP，二者除了整秒之外，还可以支持小数点后的部分，最多到 microseconds （6位）精度。格式为 'YYYY-MM-DD hh:mm:ss[.fraction]'，比如 '2038-01-19 03:14:07.999999' （事实上这也是 TIMESTAMP 能支持的最大值）。

除此之外，二者也都支持 自动初始化（Automatic Initialization)。这里要用到的两个大杀器：

二者可以同时出现，也可以单独出现，分几种情况：

同时出现CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); 复制代码

此时 ts 和 dt 的默认值就是当前时间，当这一行其他值发生变化时mysql索引表，也会自动把这两个属性更新为当前时间。

只有 DEFAULTCREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ); 复制代码

此时只有初始化的时候才会写入当前时间，随后更新时不会变动。（当然，我们也可以把 CURRENT_TIMESTAMP 换成一个常数，比如 0，语法上是支持的，只不过那样就不是当前时间了）

只有 ON UPDATECREATE TABLE t1 ( ts1 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- default 0 ts2 TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP -- default NULL ); 复制代码

此时没有指定默认值，但发生更新时会改为当前时间，这时的默认值就是 type dependent，依赖类型了。 TIMESTAMP 的默认值为 0，如果定义了 NULL 则默认值为 NULL。

CREATE TABLE t1 ( dt1 DATETIME ON UPDATE CURRENT_TIMESTAMP, -- default NULL dt2 DATETIME NOT NULL ON UPDATE CURRENT_TIMESTAMP -- default 0 ); 复制代码

这次我们换成了 DATETIME，二者正好相反，不指定 DEFAULT 的话，默认值为 NULL，但如果我们声明了 NOT NULL，则默认值变成 0。

TIMESTAMPMySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.)

TIMESTAMP 底层采用 4 个字节存储（2的31次方-1，还记得么），能支持的时间范围比 DATETIME 要小一倍，但它的特点在于，当我们写入时，MySQL会根据当前 server 所在的时区进行转换，将值变成 UTC 时区的时间，再存储。同样的，在查询的时候，MySQL 也会帮助我们转成当前时区再展示。这是 DATETIME 不具备的。

这样的跨时区支持，在一些业务场景下是很有用的。毕竟存储时间这件事情本身是很敏感的。海外用户一开始请求到了新加坡机房，落了一个时间。随后跑到欧洲玩耍，在法国重新访问，发现跟本地时间完全对不上，这就有问题了。

所以 TIMESTAMP 的思路就是，大家都以 UTC 时间为准，这是个基线，不管你是哪个时区的，我都要转成统一的时间，查询的时候给你转回去就是了。

我们可以用 show variables like '%time_zone%'; 来查看当前库的时区：

需要注意，当MySQL参数time_zone=system时，查询timestamp字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会导致线程上下文频繁切换，CPU使用率暴涨，系统响应变慢设置假死。

The time zone can be set on a per-connection basis, as described in MySQL Server Time Zone Support.

使用 SET TIME_ZONE = 'america/new_york"; 来设置时区。每个连接可以使用不同的时区

可以实验一下，在一个时区写入 TIMESTAMP 数据，切换时区后读出来，显示的时间是不一样的，而 DATETIME 则是完全一致的。demo

DATETIME

DATETIME 底层采用 8 个字节存储，没有跨时区的支持，结果直接展示。你存进去的是什么时间，读到的就是什么时间。不过我们如果需要跨时区，也不是没有办法，可以在读出来 DATETIME 后转为时间戳，从业务代码层面来处理，想转成什么时区都 OK。

这里不用担心 2038 年的限制，虽然空间大了一倍，但通常情况下不会造成多大性能影响。

Integer

这里在讨论完 DATETIME, TIMESTAMP 之后，我们回过头来看看 Integer。

为什么我们能用一个整数来代表时间呢？这里本质是我们给它赋予了【时间戳】的语义。

虽然整数的上下限更大（比如我们用 BIGINT，可以支持 2 的 63 次方 - 1 的数据），但是，但是，用法是关键。

如果你打算还用时间戳函数进行生成和转换，那就需要关注 2038 年这个限制，本质上和 TIMESTAMP 是没有区别的。

所以，通常我们认为，用整型时间戳的形式，取值范围也是 1970 年 1 月 1日起，到 2038 年截止，这个区间。用 BIGINT 的意义不大，只要它的语义还是时间戳，就需要遵循这个规范。

BETWEEN 查询

回到我们一开始提到的案例，我们需要筛选出两个时间点之间，有哪些【变更记录】。

如果是整型，我们其实经常使用 BETWEEN 来进行查询：

SELECT * FROM contacts WHERE contact_id BETWEEN 100 AND 200; 复制代码

它和下面直接用运算符的形式是等价的，注意 BETWEEN 是个闭区间：

SELECT * FROM contacts WHERE contact_id >= 100 AND contact_id ='2011-03-17 06:42:10' and created_at UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较）> timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime) 。 对于 MyISAM 引擎，建立索引的情况下，效率从高到低：UNIXTIMESTAMP(timestamp) > int > datetime（直接和时间比较）>timestamp（直接和时间比较）>UNIXTIMESTAMP(datetime) 。 对于 InnoDB 引擎，没有索引的情况下(不建议)，效率从高到低：int > UNIXTIMESTAMP(timestamp) > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(datetime)。 对于 InnoDB 引擎，建立索引的情况下，效率从高到低：int > datetime（直接和时间比较） > timestamp（直接和时间比较）> UNIXTIMESTAMP(timestamp) > UNIXTIMESTAMP(datetime)。 一句话，对于 MyISAM 引擎，采用 UNIX_TIMESTAMP(timestamp) 比较；对于InnoDB 引擎，建立索引，采用 int 或 datetime直接时间比较。

大家可以尝试一下，结合你的业务场景，跑一下 explain 看看。

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!