MySQL的 中你应该使用什么数据类型表示时间.docx

MySQL 中你应该使用什么数据类型表示时间？当你需要保存日期时间数据时，一个问题来了：你应该使用 MySQL 中的什么类型？使用 MySQL 原生的 DATE 类型还是使用 INT 字段把日期和时间保存为一个纯数字呢？在这篇文章中，我将解释 MySQL 原生的方案，并给出一个最常用数据类型的对比表。我们也将对一些典型的查询做基准测试，然后得出在给定场景下应该使用什么数据类型的结论。如果你想直接看结论，请翻到文章最下方。原生的 MySQL Datetime 数据类型Datetime 数据表示一个时间点。这可以用作日志记录、物联网时间戳、日历事件数据，等等。MySQL 有两种原生的类型可以将这种信息保存在单个字段中：Datetime 和 Timestamp。MySQL 文档中是这么介绍这些数据类型的：DATETIME 类型用于保存同时包含日期和时间两部分的值。MySQL 以 ‘YYYY-MM-DD HH:MM:SS’ 形式接收和显示 DATETIME 类型的值。TIMESTAMP 类型用于保存同时包含日期和时间两部分的值。DATETIME 或 TIMESTAMP 类型的值可以在尾部包含一个毫秒部分，精确度最高到微秒（6 位数）。TIMESTAMP 和 DATETIME 数据类型提供自动初始化和更新到当前的日期和时间的功能，只需在列的定义中设置 DEFAULT CURRENTTIMESTAMP 和 ON UPDATE CURRENTTIMESTAMP。作为一个例子：CREATETABLE`datetime_example` (??`id`int(11) NOTNULL AUTO_INCREMENT,??`measured_on` datetime NOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,??PRIMARY KEY (`id`),??KEY`measured_on` (`measured_on`)) ENGINE=InnoDB;CREATETABLE`timestamp_example` (??`id`int(11) NOTNULL AUTO_INCREMENT,??`measured_on`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,??PRIMARY KEY (`id`),??KEY`measured_on` (`measured_on`)) ENGINE=InnoDB;除了原生的日期时间表示方法，还有另一种常用的存储日期和时间信息的方法。即使用 INT 字段保存 Unix 时间（从1970 年 1 月 1 日协调世界时（UTC）建立所经过的秒数）。MySQL 也提供了只保存时间信息中的一部分的方式，通过使用 Date、Year 或 Time 类型。由于这篇文章是关于保存准确时间点的最佳方式的，我们没有讨论这些不那么精确的局部类型。使用 INT 类型保存 Unix 时间使用一个简单的 INT 列保存 Unix 时间是最普通的方法。使用 INT，你可以确保你要保存的数字可以快速、可靠地插入到表中，就像这样：INSERTINTO`vertabelo`.`sampletable`(`id`,`measured_on` ### INT类型的列)VALUES(1,946684801### 至01/01/2000 @ 12:00am (UTC) 的 UNIX 时间戳);这就是关于它的所有内容了。它仅仅是个简单的 INT 列，MySQL 的处理方式是这样的：在内部使用 4 个字节保存那些数据。所以如果你在这个列上使用 SELECT 你将会得到一个数字。如果你想把这个列用作日期进行比较，下面的查询并不能正确工作：SELECT????id, measured_on, FROM_UNIXTIME(measured_on)FROM????timestampmeasuresWHERE????measured_on 2016-01-01 ### measured_on 会被作为字符串比较以进行查询LIMIT5;这是因为 MySQL 把 INT 视为数字，而非日期。为了进行日期比较，你必须要么获取（ LCTT 译注：从 1970-01-01 00:00:00）到 2016-01-01 经过的秒数，要么使用 MySQL 的 FROM_UNIXTIME() 函数把 INT 列转为 Date 类型。下面的查询展示了 FROM_UNIXTIME() 函数的用法：SELECT????id, measured_on, FROM_UNIXTIME(measured_on)FROM????timestampme