何时使用VARCHAR和DATE/DATETIME

Question

何时使用VARCHAR和DATE/DATETIME

sqldate

41

我们在Freenode进行了这次编程讨论，当我试图使用VARCHAR（255）来存储此格式的日期变量：D/MM/YYYY时，这个问题出现了。那么问题是为什么使用VARCHAR来存储日期会很糟糕呢？以下是一些优点：

编码速度更快。以前我使用DATE，但日期格式化真的很麻烦。
使用字符串比使用日期更加耗费计算资源？谁在乎，我们生活在Ghz时代。
从伦理上讲不正确（lolwut？）这是另一个用户告诉我的……

那么，您更喜欢使用哪种方式来存储日期？SQL VARCHAR还是SQL DATE？

- mahen23

显然，SQL是第一种具有时间数据类型的语言（在此之前，每个人都使用文本表示日期，而Y2K问题实际上从未出现过，不是吗？）如果不在适当的情况下使用它们，那就是对SQL设计者的侮辱，至少其中一个是我的Facebook好友。 - onedaywhen

1

好问题！我已经审查了答案。我已经使用了所有建议。我不认同“总是使用日期类型列”的答案。使用VARCHAR列有其有效的理由。事实上，如果空间不是问题，您可能会发现最好的答案是使用各种数据类型来存储日期时间值的组合。 - Dale

5个回答

17

当你的数据库有超过2-3百万行时，你就会明白为什么使用DATETIME比VARCHAR更好了 :)

简单来说，对于数据库而言，处理能力不再是问题。只有由于HDD寻道时间导致的数据库大小才是麻烦所在。

基本上，在现代硬盘上，如果读取的顺序是随机的（通常是这种情况），则每秒可以读取约100个记录，因此您必须尽一切可能减小数据库大小，因为：

HDD的磁头不必“旅行”那么远
您将在RAM中存储更多数据

最终，总是HDD的寻道时间会让您不堪重负。例如，带有许多行的简单GROUP BY查询在磁盘上执行可能需要几个小时，而在RAM中执行则只需几秒钟=>因为寻道时间。

对于VARCHAR，您无法进行任何搜索操作。如果您非常讨厌SQL处理日期的方式，只需在32位整数字段中使用Unix时间戳即可。您将拥有（基本上）使用SQL DATE字段的所有优点，只需使用所选的编程语言操纵和格式化日期，而不是使用SQL函数。

- Slawek

3

当然，如果您将其存储在32位整数字段中，您还需要注意2038年问题。 - Powerlord

谢谢你提供的时代（epoch）的想法，操作日期让我发疯 :) - Andrew Schultz

6

两个原因：

按日期排序结果
不敏感于日期格式更改

让我们以一个看起来像这样的记录集为例：

5/12/1999 | Frank N Stein
1/22/2005 | Drake U. La
10/4/1962 | Goul Friend

如果按照您的方式存储数据，但按日期升序排序，SQL将返回以下结果集：

1/22/2005 | Drake U. La
10/4/1962 | Goul Friend
5/12/1999 | Frank N. Stein

如果我们将日期存储为DATETIME，SQL将正确响应按此顺序排序：

10/4/1962 | Goul Friend
5/12/1999 | Frank N. Stein
1/22/2005 | Drake U. La

此外，如果在某个时候您需要以不同的格式显示日期，例如像YYYY-MM-DD这样的格式，那么您需要转换所有数据或处理混合内容。当它被存储为SQL DATE时，您被迫在代码中进行转换，并且很可能只有一个位置可以更改格式以免费显示所有日期。

- Berin Loritsch

请参见我下面关于ISO 8601的答案。 - Nicholas Carey

这绝对不是一个理由。使用适当的日期格式，例如yyyy/MM/dd HH:mm:ss.SSS，这些值绝对可以排序。我真正喜欢在数据库中使用字符串作为日期的原因是它们易于阅读，无论您在哪个时区阅读它们。我只需确保将UTC日期时间以字符串形式放入，然后无论该数据库位于世界何处，我都知道那个时间是什么。显示实际日期时间值（而不是看起来像它们的字符串）的数据库工具喜欢将该值转换为您的本地时间，这并不总是有帮助的。 - Dale

@Dale，不要被欺骗以为数据总是以相同的日期格式插入。我曾经在许多系统上工作过，这些系统已经投入使用超过十年了。有人会有一个好主意去改变一些东西，并且他们会假设数据库中的格式实际上是日期时间类型...因为那是正确的事情要做的。 - Berin Loritsch

@Berin 谢谢。 :-) 我同意，但是添加具有不同日期格式的数据将是一个错误，而不是一个好主意，除非花时间更新所有现有数据到新格式，并同时调整依赖于旧格式的所有现有逻辑。 - Dale

啊，你没有任何要求来支持其他语言环境的用户、计算日期之间的时间跨度或者其他常见的日期处理需求吗？比如基本的将时间戳显示为用户所在时区的功能。如果你只是在构建一个玩具或者概念验证，那么随便你怎么做。但是如果你正在构建一个全球使用的产品，那么请使用能够帮助你实现这一目标的工具。 - Berin Loritsch

4

在日期方面，我建议使用DATE/DATETIME而不是VARCHAR。但是有一个被忽视的第三种选择——将其存储为无符号整数INTEGER unsigned！在我的上一个项目中，我决定使用INTEGER unsigned，对于传递客户端和服务器之间的日期来说，这是最理想的类型。与其每次选择时都要将其转换回DATE，不如直接选择并按照需要使用它。如果您想将日期选择为“人类可读”的日期，则可以使用FROM_UNIXTIME()函数。此外，整数占用4个字节，而DATETIME占用8个字节，节省了50%的存储空间。使用整数作为日期的存储方式也解决了Berin提出的排序问题。

- Josua Pedersen

1

请注意，datetime数据类型是一个整数（实际上是两个）：最左边的是自历元以来的天数，最右边的是自日开始的毫秒滴答数（00:00:00.000）。SQL Server日历的纪元（用日历来讲的零点）是1900年1月1日00: 00:00.000——这就是为什么convert(datetime,'')会产生一个1900年1月1日的datetime值。 - Nicholas Carey

4

我会选择使用日期/时间类型，这样可以保持简单和一致性。

如果您将其存储为字符字符串，请使用ISO 8601格式进行存储。

ISO 8601日期/时间字符串在许多方面都很有用：（A）可以正确排序，（B）易于阅读，（C）与语言环境无关，（D）可轻松转换为其他格式。引用ISO的说法，ISO 8601字符串提供了...

以下是日期和时间相关的表示方式：

- 日期 - 白天时间 - 协调世界时（UTC） - 带有与UTC的偏移的本地时间 - 日期和时间 - 时间间隔 - 循环时间间隔

这些表示方式可以采用两种格式之一：基本格式和扩展格式。基本格式字符最少，扩展格式则增加了一些字符以提高可读性。例如，2003年1月3日可以表示为20030103或2003-01-03。

相比于许多本地使用的表示方式，上述表示方式具有以下优点：

- 系统易读、易写 - 易于比较和排序 - 不受语言影响 - 大单位在小单位前面书写 - 对于大多数表示方式，符号短且长度恒定

如果你只需要存储日期，那么将其存储在ISO 8601短格式YYYYMMDD的char(8)列中所需的存储空间与datetime值相同（而且无需担心一天的最后一个时刻和下一天的第一个时刻之间的3毫秒间隙问题）。但这是另一个讨论的问题。如果将其分成三列 — YYYY char(4), MM char(2), DD char(2)，所使用的存储空间相同，并获得更多的索引选项。更好的方法是将字段存储为yyyy的short（4个字节），每个MM和DD都使用tinyint — 现在你只需要6个字节即可存储日期。当然，将日期组件分解为其组成部分的缺点是将其转换为适当的日期/时间数据类型比较复杂。

- Nicholas Carey

太棒了！FYI，我选择在数据库表中多次使用VARCHAR，原因如上所述。（我还使用日期类型列和数字类型列来存储UNIX纪元时间值）这完全取决于问题的性质。当我使用VARCHAR列时，我总是将时间存储为UTC，以便查看值时永远不会有任何混淆，并且始终可以轻松地转换为日期类型对象。 - Dale

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Kramii · Accepted Answer

为什么不用锤子钉螺丝呢？

因为锤子不是适合这项工作的正确工具。

VARCHAR版本的一些缺点：

无法轻松地添加/减去日期到VARCHAR版本中。
很难提取月份/年份。
没有阻止您在数据库的VARCHAR列中输入非日期数据。
VARCHAR版本具有文化特定性。
无法轻松排序日期。
如果以后想要更改格式，则比较困难。
这是不寻常的，这将使其他开发人员难以理解。
在许多环境中，使用VARCHAR将占用更多的存储空间。对于少量的数据可能并不重要，但在具有数百万行数据的商业环境中，这可能会产生很大的差异。

当然，在您的个人项目中，您可以随意选择。在专业环境中，我坚持使用适合这项工作的正确工具。