MySQL是如何存储日期时间值的？

Question

我是新手，对数据库领域不太熟悉。我从Java程序中发送一个datetime值，例如“2016-04-27 09:00:00”，以保存到MySQL数据库中。我的问题是，这个值将如何保存到MySQL db表的datetime类型字段中？我的意思是，它是否会像我传递的字符串一样被存储为字符串，还是MySQL将解析该字符串，并构造出UTC时间戳并将该数字存储到db中。此外，无论mysql服务器和java服务器的不同时区设置如何，这个存储的值是否会像从Java程序中检索出来一样？ 对不起，问了这么愚蠢的问题，但是这让我很困扰，在互联网上搜索时没有简单且不易混淆的答案。

- Mangu Singh Rajpurohit

---

这篇博客文章或许可以回答您的问题：日期是如何存储的？ - Phiter

本地类型以本地格式存储。 - Gordon Linoff

你能更详细地解释一下“它正在让我很痛苦，而且...”吗？ - Drew

Answer 1

来自10.9 日期和时间数据类型表示：

DATETIME非小数部分的编码：

 1 bit  sign           (1= non-negative, 0= negative)
17 bits year*13+month  (year 0-9999, month 0-12)
 5 bits day            (0-31)
 5 bits hour           (0-23)
 6 bits minute         (0-59)
 6 bits second         (0-59)
---------------------------
40 bits = 5 bytes

- shaoyihe

---

从5.6.4版本更改为https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html，MySQL服务器源代码在此处https://github.com/mysql/mysql-server/blob/ea1efa9822d81044b726aab20c857d5e1b7e046a/mysys/my_time.cc#L1863。 - undefined

Answer 2

MySQL 5.6.4及以上版本的datetime数据类型保存方式如手册页面“日期和时间数据类型表示”所述。简而言之，它是一个包括小数秒的5字节格式，并且不包含时区信息。

测试表格

create table dtTest
(
    id int auto_increment primary key,
    dt datetime not null
);

insert dtTest (dt) values ('2016-04-27 09:00:00');

如果需要确认，可以跳转到保存文件的十六进制编辑器并验证该日期时间的大端格式。

show variables like '%datadir%';
C:\ProgramData\MySQL\MySQL Server 5.6\Data\

遍历文件夹和数据库表(dtTest.ibd)

有关时区的系统和会话变量:

show variables like '%system_time_zone%';
Eastern Daylight Time

show variables like '%time_zone%';
SYSTEM

让我明确一点，我完全不了解这些的重要性。希望同行能够提供更清晰的解释。

SELECT @@global.time_zone, @@session.time_zone;
SYSTEM    SYSTEM

测试将重置为不同的值：

SET SESSION time_zone = '+10:00';
select * from dtTest;

注意，以上设置更改不影响数据的输出。它仍然是原始的并且与时区无关。

SET SESSION time_zone = 'America/Chicago';

为了使上述工作正常，请参见：

http://dev.mysql.com/doc/refman/5.6/en/mysql-tzinfo-to-sql.html

否则，如果没有加载该时区支持，则需要执行以下操作：

SET SESSION time_zone = '+10:00';

SELECT @@global.time_zone, @@session.time_zone;
SYSTEM      +10:00

将会话设置为我的默认值：

SET SESSION time_zone = 'SYSTEM';

---

SELECT COUNT(*) FROM mysql.time_zone_name;

我的计数为0，因为我还没有加载时区支持表

SELECT CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central');
-- so this is NULL for me. No support table loaded yet on my system.

SELECT CONVERT_TZ('2007-03-11 3:00:00','-4:00','-7:00');
2007-03-11 00:00:00 -- means that 3am NYC is midnight in San Fran

因此，上述内容不为NULL（对于阅读UTC时间偏移量不太友好。硬编码）。

SELECT id, CONVERT_TZ(dt,'-4:00','-7:00') from dtTest;  -- dt was my column name
1   2016-04-27 06:00:00

上面的选择语句获取数据库中的9am值，可以将其从纽约时间转换为旧金山时间。

这是我为您提供的最好的内容。希望有人可以提供缺失的时区支持细节。

- Drew