mysql时长用什么表示_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 时间戳 http://unixtimestamp.com

);

这就是关于它的所有内容了。它仅仅是个简单的 INT 列，MySQL 的处理方式是这样的：在内部使用 4 个字节保存那些数据。所以如果你在这个列上使用 SELECT 你将会得到一个数字。如果你想把这个列用作日期进行比较，下面的查询并不能正确工作：

SELECT

id, measured_on, FROM_UNIXTIME(measured_on)

FROM

vertabelo.inttimestampmeasures

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() 函数的用法：

SELECT

id, measured_on, FROM_UNIXTIME(measured_on)

FROM

vertabelo.inttimestampmeasures

WHERE

FROM_UNIXTIME(measured_on) > '2016-01-01'

LIMIT 5;

这会正确地获取到日期在 2016-01-01 之后的记录。你也可以直接比较数字和 2016-01-01 的 Unix 时间戳表示形式，即 1451606400。这样做意味着不用使用任何特殊的函数，因为你是在直接比较数字。查询如下：

SELECT

id, measured_on, FROM_UNIXTIME(measured_on)

FROM

vertabelo.inttimestampmeasures

WHERE

measured_on > 1451606400

LIMIT 5;

假如这种方式不够高效甚至提前做这种转换是不可行的话，那该怎么办？例如，你想获取 2016 年所有星期三的记录。要做到这样而不使用任何 MySQL 日期函数，你就不得不查出 2016 年每个星期三的开始和结束时间的 Unix 时间戳。然后你不得不写很大的查询，至少要在 WHERE 中包含 104 个比较。(2016 年有 52 个星期三，你不得不考虑一天的开始(0:00 am)和结束(11:59:59 pm)…)

结果是你很可能最终会使用 FROM_UNIXTIME() 转换函数。既然如此，为什么不试下真正的日期类型呢？

使用 Datetime 和 Timestamp

Datetime 和 Timestamp 几乎以同样的方式工作。两种都保存日期和时间信息，毫秒部分最高精确度都是 6 位数。同时，使用人类可读的日期形式如 “2016-01-01″ (为了便于比较)都能工作。查询时两种类型都支持“宽松格式”。宽松的语法允许任何标点符号作为分隔符。例如，”YYYY-MM-DD HH:MM:SS” 和 “YY-MM-DD HH:MM:SS” 两种形式都可以。在宽松格式情况下以下任何一种形式都能工作࿱