在MySQL日期字段中存储不完整的日期 [英] Store incomplete date in MySQL date field

问题描述

我想向用户提供引入不完整的日期的可能性（例如仅在2005年或1998年1月），但也可以按年，月，日完成日期。可以使用数据库中的单个日期字段来完成此操作吗？

我尝试了更新表A SET mydate ='2001-00-00'，似乎工作错误），但我不知道这是否正确。在内部我假设使用时间戳，所以MySQL如何表示这些0？

解决方案

的http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format\">文档允许不完整的日期：

由于
的事实，月份和日期说明符的范围从零开始，因为MySQL允许存储不完整的日期，例如
'2014-00-00'。

正如 @ wonk0 ，MySQL将无效日期设置为 0000-00-00 00:00 ：00 当 ALLOW_INVALID_DATES 未设置。然而，即使没有设置 ALLOW_INVALID_DATES ，将一个有效年份插入到零和一天也不会触发这种行为 - 至少不是在我的测试中（MySQL 5.1 .54-1ubuntu4）。我以前使用过这个功能没有任何问题，但到目前为止，我还没有找到任何更详细的文档来描述这个行为。

日期/时间比较似乎按预期工作：例如 2011-01-00> 2011-00-00 和 2011-00-01> 2011-00-00 做你所期望的。

更新

请参阅此答案（由另一个麦克）提出类似的问题。它指向了从 MySQL 5的最终指南

的摘录：

在旧版本的MySQL中，DATE和DATETIME数据类型只有
a有限的类型检查数量。通常，允许
个月的0到12之间的值，以及0和31天的值。但是，客户端程序负责提供正确的数据是
。 （对于
示例，0是一个月或一天的允许值，以便
提供存储不完整或未知数据的可能性。）

从MySQL 5.0.2开始，有一个更彻底的验证，所以
只能存储有效的数据。仍然允许的是月份和
日值0，以及日期0000-00-00。

I want to offer the user the possibility to introduce dates that are incomplete (for example just the year 2005, or Jan 1998) but also complete dates with year, month, day. Can I accomplish this using a single date field in the DB?

I have tried UPDATE tableA SET mydate = '2001-00-00' and it seems to work (no errors) but I'm not sure if this is correct. Internally I assume a timestamp is used so how can MySQL represent those 0's?

解决方案

There's a small note in the documentation for the DATE_FORMAT() about MySQL allowing incomplete dates:

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.

As pointed out by @wonk0, MySQL will sets invalid dates to 0000-00-00 00:00:00 when ALLOW_INVALID_DATES is not set. However, even when ALLOW_INVALID_DATES is not set, inserting a valid year with month and day set to zero does not appear to trigger this behaviour - at least not in my testing (MySQL 5.1.54-1ubuntu4). I have used this feature before without any problem, but I have so far been unable to find any more detailed documentation that describes this behaviour in full.

Date/Time comparisons also appear to work as expected: for example, 2011-01-00 > 2011-00-00 and 2011-00-01 > 2011-00-00 do as you would expect.

UPDATE

See this answer (by another Mike) to a similar question. It points to an extract from The Definitive Guide to MySQL 5:

In older versions of MySQL, the DATE and DATETIME data types did only a limited amount of type checking. Values between 0 and 12 for months, and 0 and 31 for days were generally allowed. However, it is the responsibility of the client program to provide correct data. (For example, 0 is a permissible value for a month or day, in order to provide the possibility of storing incomplete or unknown data.)

Beginning with MySQL 5.0.2, there is a more thorough validation, so that only valid data can be stored. Still allowed are the month and day values 0, as well as the date 0000-00-00.

这篇关于在MySQL日期字段中存储不完整的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！