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。 作为一个例子： CREATE TABLE `datetime_example` ( ??`id` int(11) NOT NULL AUTO_INCREMENT, ??`measured_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ??PRIMARY KEY (`id`), ??KEY `measured_on` (`measured_on`) ) ENGINE=InnoDB; CREATE TABLE `timestamp_example` ( ??`id` int(11) NOT NULL AUTO_INCREMENT, ??`measured_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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，你可以确保你要保存的数字可以快速、可靠地插入到表中，就像这样： INSERT INTO `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 ????timestampmeasures WHERE ????measured_on 2016-01-01 ### measured_on 会被作为字符串比较以进行查询 LIMIT 5; 这是因为 MySQL 把 INT 视为数字，而非日期。为了进行日期比较，你必须要么获取（ LCTT 译注：从 1970-01-01 00:00:00）到 2016-01-01 经过的秒数，要么使用 MySQL 的 FROM_UNIXTIME() 函数把 INT 列转为 Date 类型。下面的查询展示了 FROM_UNIXTIME() 函数