mysql - 在数据库中存储日期期间

Question

我想讨论在数据库中存储日期期间的“最佳”方式。说说SQL/MySQL吧，不过这个问题可能是针对任何数据库的。我有一种感觉，我多年来一直在做错事......

英文的话，我掌握的信息是:

-In year 2014, value is 1000
-In year 2015, value is 2000
-In year 2016, there is no value
-In year 2017 (and go on), value is 3000

有人可能存储为:

BeginDate   EndDate     Value
2014-01-01  2014-12-31  1000
2015-01-01  2015-12-31  2000
2017-01-01  NULL        3000

其他人可能存储为:

Date        Value
2014-01-01  1000
2015-01-01  2000
2016-01-01  NULL
2017-01-01  3000

• 第一种方法验证规则看起来像是为了避免漏洞和重叠而开发的困惑。
• 在第二种方法中，问题似乎是在一段时间内过滤一个准时日期。

我的同事喜欢什么？还有其他建议吗？

编辑:例如，我只使用全年，我的数据通常以天为粒度变化。

编辑 2: 我考虑过将存储的“日期”用作“开始日期”，按日期对行进行排序，然后在下一行(或上一行)中选择“结束日期”。作为方法一，存储“BeginDate”和“Interval”会导致漏洞/重叠问题，我需要一个复杂的验证规则来避免。

Answer 1

这主要取决于您使用这些信息的方式 - 我假设您所做的不仅仅是在数据库中存储一年的值。

这里有很多猜测，但我猜你还有其他带有时间限制数据的表，你需要比较日期以找到匹配项。

例如，在您当前的架构中:

select *
from other_table ot
inner join year_table yt on ot.transaction_date between yt.year_start and yt.year_end

这应该是一个易于优化的查询 - 它是一个直接的数据比较，如果表足够大，您可以添加索引以加快速度。

在您的第二个架构建议中，这并不容易:

select *
from other_table ot
inner join year_table yt 
 on ot.transaction_date between yt.year_start 
 and yt.year_start + INTERVAL 1 YEAR

至关重要的是 - 这更难优化，因为每次比较都需要执行标量函数。这可能无关紧要 - 但对于大型表或更复杂的查询，它可能成为瓶颈。

您还可以将年份存储为整数(正如某些评论者所建议的那样)。

select *
from other_table ot
inner join year_table yt on year(ot.transaction_date) = yt.year

同样 - 这可能会对性能产生影响，因为每次比较都需要执行一个函数。

我这个纯粹主义者不喜欢将其存储为整数 - 所以您也可以使用 MySQL 的 YEAR datatype .

因此，假设数据大小不是您要优化的问题，那么解决方案实际上在于此表中的数据与架构的其余部分相关的方式。