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
);
这就是关于它的所有内容了。它仅仅是个简单的 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
mysql下载什么版本的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” 两种形式都可以。在宽松格式情况下以下任何⼀种形式都能⼯作：
2012-12-31 11:30:45
2012^12^31 11+30+45
2012/12/31 11*30*45
2012@12@31 11^30^45
其它宽松格式也是允许的；你可以在 MySQL 参考⼿册 到所有的格式。
默认情况下，Datetime 和 Timestamp 两种类型查询结果都以标准输出格式显⽰ —— 年-⽉-⽇ 时：分：秒 (如 2016-01-01 23:59:59)。如果使⽤了毫秒部分，它们应该以⼩数值出现在秒后⾯ (如 2016-01-01 23:59:59.5)。
Timestamp 和 Datetime 的核⼼不同点主要在于 MySQL 在内部如何表⽰这些信息：两种都以⼆进制⽽⾮字符串形式存储，但在表⽰⽇期/时间部分时 Timestamp (4 字节) ⽐ Datetime (5 字节) 少使⽤ 1 字节。当保存毫秒部分时两种都使⽤额外的空间 (1-3 字节)。如果你存储 150 万条记录，这种 1 字节的差异是微不⾜道的：
150 万条记录 * 每条记录 1 字节 / (1048576 字节/MB) = 1.43 MB
Timestamp 节省的 1 字节是有代价的：你只能存储从 ‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’之间的时间。⽽ Datetime 允许你存储从 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’ 之间的任何时间。
另⼀个重要的差别 —— 很多 MySQL 开发者没意识到的 —— 是 MySQL 使⽤服务器的时区转换 Timestamp 值到它的 UTC 等价值再保存。当获取值是它会再次进⾏时区转换，所以你得回了你“原始的”⽇期/时间值。有可能，下⾯这些情况会发⽣。
理想情况下，如果你⼀直使⽤同⼀个时区，MySQL 会获取到和你存储的同样的值。以我的经验，如果你的数据库涉及时区变换，你可能会遇到问题。例如，服务器变化(⽐如，你把数据库从都柏林的⼀台服务器迁移到加利福尼亚的⼀台服务器上，或者你只是修改了⼀下服务器的时区)时可能会发⽣这种情况。不管哪种⽅式，如果你获取数据时的时区是不同的，数据就会受影响。
Datetime 列不会被数据库改变。⽆论时区怎样配置，每次都会保存和获取到同样的值。就我⽽⾔，我认为这是⼀个更可靠的选择。
MySQL ⽂档：
MySQL 把 TIMESTAMP 值从当前的时区转换到 UTC 再存储，获取时再从 UTC 转回当前的时区。(其它类型如 DATETIME 不会这样，它们会“原样”保存。) 默认情况下，每个连接的当前时区都是服务器的时区。时区可以基于连接设置。只要时区设置保持⼀致，你就能得到和保存的相同的值。如果你保存了⼀个 TIMESTAMP 值，然后改变了时区再获取这个值，获取到的值和你存储的是不同的。这是因为在写⼊和查询的会话上没有使⽤同⼀个时区。当前时区可以通过系统变量 time_zone 的值得到。更多信息，请查看
MySQL Server Time Zone Support。
对⽐总结
在深⼊探讨使⽤各数据类型的性能差异之前，让我们先看⼀个总结表格以给你更多了解。每种类型的弱点以红⾊显⽰。
基准测试 INT、Timestamp 和 Datetime 的性能
为了⽐较这些类型的性能，我会使⽤我创建的⼀个天⽓预报⽹络的 150 万记录(准确说是 1,497,421)。这个⽹络每分钟都收集数据。为了让这些测试可复现，我已经删除了⼀些私有列，所以你可以使⽤这些数据运⾏你⾃⼰的测试。
基于我原始的表格，我创建了三个版本：
datetimemeasures 表在 measured_on 列使⽤ Datetime 类型，表⽰天⽓预报记录的测量时间
timestampmeasures 表在 measured_on 列使⽤ Timestamp 类型
inttimestampmeasures 表在 measured_on 列使⽤ INT (unsigned) 类型
这三个表拥有完全相同的数据；唯⼀的差别就是 measured_on 字段的类型。所有表都在 measured_on 列上设置了⼀个索引。
基准测试⼯具
为了评估这些数据类型的性能，我使⽤了两种⽅法。⼀种基于 Sysbench，它的官⽹是这么描述的：
“… ⼀个模块化、跨平台和多线程的基准测试⼯具，⽤以评估那些对运⾏⾼负载数据库的系统⾮常重要的系统参数。”
这个⼯具是 MySQL ⽂档中推荐的。
如果你使⽤ Windows (就像我)，你可以下载⼀个包含可执⾏⽂件和我使⽤的测试查询的 zip ⽂件。它们基于 ⼀种推荐的基准测试⽅法。
为了执⾏⼀个给定的测试，你可以使⽤下⾯的命令(插⼊你⾃⼰的连接参数)：
sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-host=localhost --MySQL-password= --test=sysbench_test_file.lua --num-threads=8 --max-requests=100 run
这会正常⼯作，这⾥ sysbench_test_file.lua 是测试⽂件，并包含了各个测试中指向各个表的 SQL 查询。
为了进⼀步验证结果，我也运⾏了 mysqlslap。它的官⽹是这么描述的：
“mysqlslap 是⼀个诊断程序，为模拟 MySQL 服务器的客户端负载并报告各个阶段的⽤时⽽设计。它⼯作起来就像是很多客户端在同时访问服务器。”
记得这些测试中最重要的不是所需的绝对时间。⽽是在不同数据类型上执⾏相同查询时的相对时间。
这两个基准测试⼯具的测试时间不⼀定相同，因为不同⼯具的⼯作⽅式不同。重要的是数据类型的⽐较，随着我们深⼊到测试中，这将会变得清楚。
基准测试
我将使⽤三种可以评估⼏个性能⽅⾯的查询：
时间范围选择
在 Datetime 和 Timestamp 数据类型上这允许我们直接⽐较⽽不需要使⽤任何特殊的⽇期函数。
同时，我们可以评估在 INT 类型的列上使⽤⽇期函数相对于使⽤简单的数值⽐较的影响。为了做到这些我们需要把范围转换为 Unix 时间戳数值。
⽇期函数选择
与前个测试中⽐较操作针对⼀个简单的 DATE 值相反，这个测试使得我们可以评估使⽤⽇期函数作为 “WHERE” ⼦句的⼀部分的性能。
我们还可以测试⼀个场景，即我们必须使⽤⼀个函数将 INT 列转换为⼀个合法的 DATE 类型然后执⾏查询。
count() 查询
作为对前⾯测试的补充，这将评估在三种不同的表⽰类型上进⾏典型的统计查询的性能。
我们将在这些测试中覆盖⼀些常见的场景，并看到三种类型上的性能表现。
关于 SQL_NO_CACHE
当在查询中使⽤ SQL_NO_CACHE 时，服务器不使⽤查询缓存。它既不检查查询缓存以确认结果是不是已经在那⼉了，也不会保存查询结果。因此，每个查询将反映真实的性能影响，就像每次查询都是第⼀次被调⽤。
测试 1：选择⼀个⽇期范围中的值
这个查询返回总计 1,497,421 ⾏记录中的 75,706 ⾏。
查询 1 和 Datetime：
SELECT SQL_NO_CACHE
measured_on
FROM
vertabelo.datetimemeasures m
WHERE
asured_on < '2016-02-01 00:00:00.0';
性能
Sysbench cmd> sysbench --MySQL-table-engine=innodb --MySQL-db=vertabelo --MySQL-user=root --MySQL-
host=localhost --MySQL-password= --test=datetime.lua --num-threads=8 --max-requests=100 run

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。