SQL 何时使用 VARCHAR 和 DATE/DATETIME

When to use VARCHAR and DATE/DATETIME

提问by mahen23

We had this programming discussion on Freenode and this question came up when I was trying to use a VARCHAR(255) to store a Date Variable in this format: D/MM/YYYY. So the question is why is it so bad to use a VARCHAR to store a date. Here are the advantages:

我们在 Freenode 上进行了这个编程讨论，当我尝试使用 VARCHAR(255) 以这种格式存储日期变量时出现了这个问题：D/MM/YYYY。所以问题是为什么使用 VARCHAR 存储日期如此糟糕。以下是优点：

1. Its faster to code. Previously I used DATE, but date formatting was a real pain.
2. Its more power hungry to use string than Date? Who cares, we live in the Ghz era.
3. Its not ethically correct (lolwut?) This is what the other user told me...

1. 它的编码速度更快。以前我使用过 DATE，但日期格式是一个真正的痛苦。
2. 使用字符串比使用日期更耗电吗？谁在乎，我们生活在 Ghz 时代。
3. 它在道德上不正确（lolwut？）这是另一个用户告诉我的......

So what would you prefer to use to store a date? SQL VARCHAR or SQL DATE?

那么你更喜欢用什么来存储日期呢？SQL VARCHAR 还是 SQL 日期？

回答by Kramii

Why not put screws in with a hammer?

为什么不用锤子拧螺丝？

Because it isn't the right tool for the job.

因为它不是适合这项工作的工具。

Some of the disadvantages of the VARCHAR version:

VARCHAR 版本的一些缺点：

• You can't easily add / subtract days to the VARCHAR version.
• It is harder to extract just month / year.
• There is nothing stopping you putting non-date data in the VARCHAR column in the database.
• The VARCHAR version is culture specific.
• You can't easily sort the dates.
• It is difficult to change the format if you want to later.
• It is unconventional, which will make it harder for other developers to understand.
• In many environments, using VARCHAR will use more storage space. This may not matter for small amounts of data, but in commercial environments with millions of rows of data this might well make a big difference.

• 您不能轻松地向 VARCHAR 版本添加/减去天数。
• 仅提取月/年比较困难。
• 没有什么可以阻止您将非日期数据放入数据库的 VARCHAR 列中。
• VARCHAR 版本是特定于文化的。
• 您不能轻松地对日期进行排序。
• 如果以后要更改格式，则很难更改。
• 这是非常规的，这将使其他开发人员更难理解。
• 在许多环境中，使用 VARCHAR 将使用更多的存储空间。这对于少量数据可能无关紧要，但在具有数百万行数据的商业环境中，这可能会产生很大的不同。

Of course, in your hobby projects you can do what you want. In a professional environment I'd insist on using the right tool for the job.

当然，在你的爱好项目中，你可以做你想做的。在专业环境中，我坚持使用正确的工具来完成工作。

回答by Slawek

When you'll have database with more than 2-3 million rows you'll know why it's better to use DATETIME than VARCHAR :)

当您拥有超过 2-3 百万行的数据库时，您就会知道为什么使用 DATETIME 比使用 VARCHAR 更好：)

Simple answer is that with databases - processing power isn't a problem anymore. Just the database size is because of HDD's seek time.

简单的答案是，对于数据库 - 处理能力不再是问题。只是数据库大小是因为 HDD 的寻道时间。

Basically with modern harddisks you can read about 100 records / second if they're read in random order (usually the case) so you must do everything you can to minimize DB size, because:

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

• The HDD's heads won't have to "travel" this much
• You'll fit more data in RAM

• 硬盘驱动器的头不必“移动”这么多
• 您将在 RAM 中容纳更多数据

In the end it's always HDD's seek times that will kill you. Eg. some simple GROUP BY query with many rows could take a couple of hours when done on disk compared to couple of seconds when done in RAM => because of seek times.

最后总是硬盘的寻道时间会杀死你。例如。由于寻道时间，一些简单的 GROUP BY 查询在磁盘上完成时可能需要几个小时，而在 RAM => 中完成时可能需要几秒钟。

For VARCHAR's you can't do any searches. If you hate the way how SQL deals with dates so much, just use unix timestamp in 32 bit integer field. You'll have (basically) all advantages of using SQL DATE field, you'll just have to manipulate and format dates using your choosen programming language, not SQL functions.

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

回答by Berin Loritsch

Two reasons:

两个原因：

• Sorting results by the dates
• Not sensitive to date formatting changes

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

So let's take for instance a set of records that looks like this:

因此，让我们以一组如下所示的记录为例：

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

If we were to store the data your way, but sorted on the dates in assending order SQL will respond with the resultset that looks like this:

如果我们按照您的方式存储数据，但按日期排序，SQL 将使用如下所示的结果集进行响应：

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

Where if we stored the dates as a DATETIME, SQL will respond correctly ordering them like this:

如果我们将日期存储为 DATETIME，SQL 将正确响应，如下所示：

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

Additionally, if somewhere down the road you needed to display dates in a different format, for example like YYYY-MM-DD, then you would need to transform all your data or deal with mixed content. When it's stored as a SQL DATE, you are forced to make the transform in code, and very likely have one spot to change the format to display all dates--for free.

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

回答by Josua Pedersen

Between DATE/DATETIMEand VARCHARfor dates I would go with DATE/DATETIMEeverytime. But there is a overlooked third option. Storing it as a INTEGER unsigned!

我每次都会去的日期之间DATE/DATETIME和VARCHAR日期DATE/DATETIME。但是还有一个被忽视的第三种选择。将其存储为未签名的整数！

I decided to go with INTEGER unsignedin my last project, and I am really satisfied with making that choice instead of storing it as a DATE/DATETIME. Because I was passing along dates between client and server it made the ideal type for me to use. Instead of having to store it as DATEand having to convert back every time I select, I just select it and use it however I want it. If you want to select the date as a "human-readable" date you can use the FROM_UNIXTIME()function.

我决定INTEGER unsigned在我的上一个项目中使用，我对做出这个选择而不是将其存储为DATE/DATETIME. 因为我在客户端和服务器之间传递日期，所以它是我使用的理想类型。不必将其存储为DATE每次选择时都必须转换回来，我只需选择它并根据需要使用它。如果您想选择日期作为“人类可读”的日期，您可以使用该FROM_UNIXTIME()功能。

Also a integer takes up 4 bytes while DATETIMEtakes up 8 bytes. Saving 50% storage.

同样一个整数占用 4 个字节，而DATETIME占用 8 个字节。节省 50% 的存储空间。

The sorting problem that Berin proposes is also solved using integer as storage for dates.

Berin 提出的排序问题也可以使用整数作为日期的存储来解决。

回答by Nicholas Carey

I'd vote for using the date/datetime types, just for the sake of simplicity/consistency.

我会投票支持使用日期/日期时间类型，只是为了简单/一致性。

If you do store it as a character string, store it in ISO 8601format:

如果确实将其存储为字符串，请以ISO 8601格式存储：

• http://www.iso.org/iso/date_and_time_format
• http://xml.coverpages.org/ISO-FDIS-8601.pdf
• http://www.cl.cam.ac.uk/~mgk25/iso-time.html

• http://www.iso.org/iso/date_and_time_format
• http://xml.coverpages.org/ISO-FDIS-8601.pdf
• http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Among other things, ISO 8601 date/time string (A) collate properly, (B) are human readable, (C) are locale-indepedent, and (D) are readily convertable to other formats. To crib from the ISO blurb, ISO 8601 strings offer

其中，ISO 8601 日期/时间字符串 (A) 正确整理，(B) 是人类可读的，(C) 与区域设置无关，并且 (D) 可以轻松转换为其他格式。为了从 ISO 简介中提取，ISO 8601 字符串提供

representations for the following:

• Date
• Time of the day
• Coordinated universal time (UTC)
• Local time with offset to UTC
• Date and time
• Time intervals
• Recurring time intervals

Representations can be in one of two formats: a basic format that has a minimal number of characters and an extended format that adds characters to enhance human readability. For example, the third of January 2003 can be represented as either 20030103 or 2003-01-03.

[and]

offer the following advantages over many of the locally used representations:

• Easily readable and writeable by systems
• Easily comparable and sortable
• Language independent
• Larger units are written in front of smaller units
• For most representations the notation is short and of constant length

以下代表：

• 日期
• 一天中的时间
• 协调世界时 (UTC)
• 本地时间与 UTC 的偏移量
• 日期和时间
• 时间间隔
• 重复时间间隔

表示可以是以下两种格式之一：具有最少字符数的基本格式和添加字符以增强人类可读性的扩展格式。例如，2003 年 1 月的第三天可以表示为 20030103 或 2003-01-03。

[和]

与许多本地使用的表示法相比，具有以下优势：

• 系统易于读取和写入
• 易于比较和排序
• 语言无关
• 较大的单位写在较小的单位前面
• 对于大多数表示，符号很短且长度恒定

One last thing: If all you need to do is store a date, then storing it in the ISO 8601 short form YYYYMMDD in a char(8) column takes no more storage than a datetime value (and you don't need to worry about the 3 millisecond gap between the last tick of the one day and the first tick of the next. But that's a matter for another discussion. If you break it up into 3 columns — YYYY char(4), MM char(2), DD char(2)you'll use up the same amount of storage, and get more options for indexing. Even better, store the fields as a short for yyyy (4 bytes), and a tinyint for each of MM and DD — now you're down to 6 bytes for the date. The drawback, of course, to decomposing the date components into their constituent parts is that conversion to proper date/time data types is complicated.

最后一件事：如果您需要做的只是存储一个日期，那么将它以 ISO 8601 简写形式 YYYYMMDD 存储在 char(8) 列中所占用的存储空间不会超过日期时间值（您无需担心一天的最后一个滴答声和下一天的第一个滴答声之间的 3 毫秒间隔。但这是另一个讨论的问题。如果你把它分成 3 列 -YYYY char(4), MM char(2), DD char(2)你将使用相同的存储量，并得到更多的索引选项。更好的是，将字段存储为 yyyy（4 个字节）的缩写，并为每个 MM 和 DD 存储一个 tinyint——现在日期减少到 6 个字节。当然，缺点是将日期组件分解成它们的组成部分是转换为正确的日期/时间数据类型很复杂。