MYSQL存储时间数据的数据类型选择
mysql语句分类今天测试发现公司某项⽬中设置⽤户出⽣⽇期的接⼝报错，查看⽇志发现是mysql⽤的timestamp存储birthday，⽽我设置的⽣⽇是1949年。这个数据在真实场景是正常的，那么问题就出在表字段的数据类型上了。
之前没有在意这些，今天发现了就记录⼀下。
⼤家在设计数据库的时候也不能⽆脑复制，需要根据业务设置最佳的数据类型和长度。
⼀、Mysql中⽤来存储⽇期的数据类型有三种：Date、Datetime、Timestamp。
1. Date数据类型：⽤来存储没有时间的⽇期。
Mysql获取和显⽰这个类型的格式为“YYYY-MM-DD”。按照标准的SQL，不允许其他格式。在UPDATE表达式以及SELECT语句的WHERE⼦句中应使⽤该格式。例如：mysql> SELECT * FROM tbl_nameWHERE date >= ‘2003-05-05’;
⽀持的范围为'1000-01-01'到'9999-12-31'。MySQL以’YYYY-MM-DD’格式显⽰DATE值，但允许使⽤字符串或数字为DATE列分配值。
2. Datetime类型：⽇期和时间的组合。
⽀持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以’YYYY-MM-DD HH:MM:SS’格式显⽰DATETIME值，但允许使⽤字符串或数字为DATETIME列分配值。
3. Timestamp类型：时间戳。
范围是'1970-01-01 00:00:00'到'2037-12-31 23:59:59。
TIMESTAMP列⽤于INSERT或UPDATE操作时记录⽇期和时间。如果你不分配⼀个值，表中的第⼀个TIMESTAMP列⾃动设置为最近操作的⽇期和时间。也可以通过分配⼀个NULL值，将TIMESTAMP列设置为当前的⽇期和时间。
TIMESTAMP值返回后显⽰为’YYYY-MM-DD HH:MM:SS’格式的字符串，显⽰宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。二维数组矩阵图
所有不符合上⾯所述格式的数据都会被转换为相应类型的0值。(0000-00-00或者0000-00-00 00:00:00)
⼆、Datetime 和 Timestamp 之间抉择
Datetime 和 Timestamp 是 MySQL 提供的两种⽐较相似的保存时间的数据类型。他们两者究竟该如何选择呢？
通常我们都会⾸选 Timestamp。 下⾯说⼀下为什么这样做!
1. DateTime 类型没有时区信息
DateTime 类型是没有时区信息的（时区⽆关） ，DateTime 类型保存的时间都是当前会话所设置的时区对应的时间。这样就会有什么问题呢？当你的时区更换之后，⽐如你的服务器更换地址或者更换客户端连接时区设置的话，就会导致你从数据库中读出的时间错误。不要⼩看这个问题，很多系统就是因为这个问题闹出了很多笑话。
Timestamp 和时区有关。Timestamp 类型字段的值会随着服务器时区的变化⽽变化，⾃动换算成相应的时间，说简单点就是在不同时区，查询到同⼀个条记录此字段的值会不⼀样。
下⾯实际演⽰⼀下！
建表 SQL 语句：pythonsort函数用法
CREATE TABLE `time_zone_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date_time` datetime DEFAULT NULL,
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
php中session和cookie的区别PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插⼊数据：
INSERT INTO time_zone_test(date_time,time_stamp)VALUES(NOW(),NOW());
查看数据：
select date_time,time_stamp from time_zone_test;bom管理系统
结果：
+---------------------+---------------------+
| date_time | time_stamp |
+---------------------+---------------------+
|2020-01-1109:53:32|2020-01-1109:53:32|
+---------------------+---------------------+
修改当前会话的时区:
set time_zone='+8:00';
再次查看数据：
+---------------------+---------------------+
| date_time | time_stamp |
+---------------------+---------------------+
|2020-01-1109:53:32|2020-01-1117:53:32|
程序员为什么要学linux
+---------------------+---------------------+
扩展：⼀些关于 MySQL 时区设置的⼀个常⽤ sql 命令
查看当前会话时区 SELECT @@session.time_zone;
设置当前会话时区 SET time_zone = ‘Europe/Helsinki’; SET time_zone = “+00:00”;
数据库全局时区设置 SELECT @@global.time_zone;
设置全局时区 SET GLOBAL time_zone = ‘+8:00’; SET GLOBAL time_zone = ‘Europe/Helsinki’;
2. DateTime 类型耗费空间更⼤
Timestamp 只需要使⽤ 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是，这样同样造成了⼀个问题，Timestamp 表⽰的时间范围更⼩。
DateTime ：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59
Timestamp 在不同版本的 MySQL 中有细微差别。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论