通常我使用datetime字段在应用程序中存储数据的创建时间和更新时间。
但现在我遇到了一个数据库表,他们在表中分别保留了日期和时间两个字段。
- 那么什么情况下应该使用这两个方案,为什么?
- 使用这两个方案有什么优缺点?
通常我使用datetime字段在应用程序中存储数据的创建时间和更新时间。
但现在我遇到了一个数据库表,他们在表中分别保留了日期和时间两个字段。
使用DATE字段比使用DATETIME字段性能差异很大。我有一个表格有超过4,000,000条记录,为了测试目的,我添加了两个字段,每个字段都具有自己的索引。一个使用DATETIME,另一个字段使用DATE。
我禁用了MySQL查询缓存以便进行正确测试,并重复执行相同的查询1000次:
SELECT * FROM `logs` WHERE `dt` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;
DATETIME INDEX:
197.564秒。
SELECT * FROM `logs` WHERE `d` BETWEEN '2015-04-01' AND '2015-05-01' LIMIT 10000,10;
DATE INDEX:
107.577秒。
使用日期索引字段可以提高性能:45.55%!!
因此,如果您希望表中有大量数据,请考虑将日期和时间分开并分别建立索引。
我认为将日期和时间存储在不同的字段中基本上没有优点。MySQL提供了非常方便的函数来提取datetime
值的日期和时间部分。
好的。可能会有一些效率原因。在MySQL中,您可以在字段上放置单独的索引。例如,如果您想要搜索特定的时间,那么按小时计数的查询(例如)可以使用time
字段上的索引。在这种情况下,datetime
字段上的索引将不会被使用。单独的date
字段可能会使编写将使用date
索引的查询更容易,但严格来说,datetime
也应该可以工作。
我见过日期和时间分开存储的唯一场景是交易系统。在这种情况下,交易有一个估值日期。估值时间是类似于“纽约开市”或“伦敦收盘”的描述性时间,而不是实时值。
麻烦的部分是在对时间值进行日期算术运算并且不想让日期部分参与其中。例如:
myapptdate = 2014-01-02 09:00:00
选择这样那样的东西,其中myapptdate在2014-01-02 07:00:00和2014-01-02 13:00:00之间
1900-01-02 07:00:00 2014-01-02 07:00:00
我发现的一个区别是在使用BETWEEN
筛选带有非零时间的日期时。
想象一下使用“日期之间”筛选器进行搜索。标准用户的期望是它将返回从结束日期开始的记录,因此使用DATETIME
,您必须始终添加额外的一天才能使BETWEEN按预期工作,而使用DATE
,您只需传递用户输入的内容,无需额外的逻辑。
所以查询:
SELECT * FROM mytable WHERE mydate BETWEEN '2020-06-24' AND '2020-06-25'
将返回2020-06-25 16:30:00
的记录,而查询语句为:
SELECT * FROM mytable WHERE mydatetime BETWEEN '2020-06-24' AND '2020-06-25'
不会的 - 你需要再加一天:
SELECT * FROM mytable WHERE mydatetime BETWEEN '2020-06-24' AND '2020-06-26'
DATE
,或者作为“缓存”以加速查询日期查找(请参见 Elwin 的回答)。