为什么MySQL time类型的最大时限为838:59:59?

问题描述

我自己已经遇到了限制，但是尽管在线上有很多闲聊，但我从未见过为什么 TIME数据类型的上限和下限是什么的解释.官方参考位于 http://dev.mysql.com/doc/refman /5.7/en/time.html 说

TIME值的范围可以从"-838:59:59"到"838:59:59".小时部分可能是如此之大，因为时间"类型不仅可以用来表示一天中的某个时间(必须少于24小时)，而且可以用来表示经过的时间或两个事件之间的时间间隔(可能远大于24小时，甚至是负面的.

但是我不知道为什么不允许小时数如此之大，而是为什么它会被截断.对于几天而言，这么多小时似乎没有任何意义，或者如果我尝试想象将整数存储多少秒的可能截止时间，则没有任何意义.那为什么范围呢?

推荐答案

TIME值始终存储在MySQL中的3个字节上.但是格式在版本5.6.4上更改了.我怀疑这不是第一次改变.但是另一种变化(如果有的话)是很久以前发生的，并且没有公开的证据. GitHub上的MySQL源代码历史记录从5.5版开始(最早的提交是在2008年5月)，但是我要查找的更改发生在2001-2002年左右(MySQL 4在2003年发布)
如文档中所述，当前格式使用6位(秒)(可能的值:0至63)，6位(分钟)，10位(小时)(可能的值:0至)，其中1位用于符号(加上已经提到的间隔的负值)，而1位未使用并标记为为将来的扩展保留".
它经过优化，可以处理时间成分(小时，分钟，秒)，并且不会浪费太多空间.使用这种格式，可以在-1023:59:59和+1023:59:59之间存储值.但是，MySQL将小时数限制为838，可能是为了与前一阵子编写的应用程序向后兼容，而我认为这是极限.

直到版本5.6.4，TIME值也存储在3个字节上，并且组件打包为days * 24 * 3600 + hours * 3600 + minutes * 60 + seconds.该格式已针对使用时间戳进行了优化(因为实际上是时间戳).使用这种格式，可以将值存储在大约-2330至+2330小时的范围内.尽管有大量可用的值，但MySQL仍将值限制为-838至+838小时.

在MySQL 4上存在错误#11655 .使用嵌套的SELECT语句在-838…+838范围之外的TIME值.它不是一个功能，而是一个错误，并且已得到修复.

向后兼容

将值限制在此范围内并主动更改任何产生TIME值的代码的唯一原因是我怀疑MySQL 3使用了另一种格式，由于数据打包的方式，其有效值限制在-838…+838小时范围内.

通过查看当前的 MySQL的源代码我发现了一个有趣的公式:

#define TIME_MAX_VALUE (TIME_MAX_HOUR10000 + TIME_MAX_MINUTE100 + TIME_MAX_SECOND)

暂时暂时忽略上面使用的名称中的 MAX 部分，让我们仅记住TIME_MAX_MINUTE和TIME_MAX_SECOND是00和59之间的数字.该公式仅将小时，分钟和秒连接成一个整数.例如，值170:29:45变为1702945.

此公式引发了以下问题:假设TIME值存储在带符号的3个字节上，用这种方式可以表示的最大正值是多少?

我们要查找的值是0x7FFFFF，十进制表示法是8388607.由于最后四位数字(8607)应该读为分钟(86)和秒(07)，并且它们的最大有效值为59，因此可以使用符号将3个字节中的最大值存储为上面的公式是8385959. TIME是+838:59:59.塔达！

猜猜是什么?上面列出的C代码片段是从中提取的:

/* Limits for the TIME data type */
#define TIME_MAX_HOUR 838
#define TIME_MAX_MINUTE 59
#define TIME_MAX_SECOND 59
#define TIME_MAX_VALUE (TIME_MAX_HOUR*10000 + TIME_MAX_MINUTE*100 + TIME_MAX_SECOND)

我确信这是MySQL 3用来内部保留TIME值的方式.这种格式强加了范围的限制，而对后续版本的向后兼容性要求将这种限制传播到了我们的时代.