我通常使用PHP的time()
函数在MySQL数据库中将日期存储为整数,而不是使用MySQL的日期格式,因为当我将其取出时更容易操作,但这样做有什么缺点吗?
范围:
显然的缺点是:你可以存储的日期范围从1970年到2038年有限。如果需要存储这个范围之外的日期,通常需要使用另一种格式。我发现最常见的情况是出生日期。
可读性:
我认为人们选择使用内置日期类型的最重要原因是数据更容易解释。你可以进行简单的查询,不必进一步格式化响应就能理解值。
索引:
使用日期类型的一个好的技术原因是它允许在某些情况下进行索引查询,而unix时间戳则不行。考虑以下查询:
SELECT * FROM tbl WHERE year(mydate_field) = 2009;
如果mydate_field是本地日期类型,并且该字段上有索引,则此查询实际上将使用索引,尽管有函数调用。这几乎是mysql唯一可以优化这种字段的函数调用的时间。对于时间戳字段的相应查询将无法使用索引:SELECT * FROM tbl WHERE year(from_unixtime(mytimestamp_field)) = 2009;
如果你仔细考虑一下,其实是有个解决方法的。这个查询语句能够完成相同的任务,而且可以利用索引优化:
SELECT * FROM tbl WHERE mytimestamp_field > unix_timestamp("2009-01-01") AND mytimestamp_field < unix_timestamp("2010-01-01");
计算:
通常情况下,我将日期存储为Unix时间戳,尽管这样做有一些不利之处。这并不是因为它本身的优点,而是因为我习惯了这种方式。我发现这简化了一些计算,但同时也使其他计算变得复杂。例如,要在Unix时间戳上加一个月非常困难,因为每个月的秒数是不同的。使用mysql DATE_ADD()函数很容易实现这一点。然而,在大多数情况下,我认为它确实简化了计算。例如,通常你可能会想要选择最近两天内的帖子。如果字段包含Unix时间戳,则只需轻松执行以下操作:
SELECT * FROM tbl WHERE mytimestamp_field > time() - 2*24*3600;
可能是个人口味问题,但我个人认为这种方法比记忆 DATE_SUB() 函数的语法更快更容易。
时区:
Unix 时间戳无法存储时区数据。我住在瑞典,只有一个时区,所以这对我来说不是真正的问题。然而,如果你住在横跨多个时区的国家,这可能会带来很大的麻烦。
一个缺点是您将无法使用SQL函数操作和查询这些日期。
UNIX时间戳在能够存储的日期范围方面具有明显的限制。
我现在总是使用DATETIME
字段。您可以使用SQL进行大量的日期计算,因此您可以提取有用的信息,例如现在和存储日期之间的DATEDIFF
,而无需使用任何PHP。
存在许多缺点:
如果需要time_t
,可以在代码中轻松转换。
细节上稍有损失。MySQL Datetime变量可以非常精确。
此外,如果你需要在数据库中比较日期,日期格式有一些内置函数是你无法使用的。
我认为出于可扩展性的原因,最好使用Unix时间戳。
优点:
UTC
时区(如果您有跨多个时区的服务器,则无需进行转换)。created
< 19345345345-24*60*60只计算一次)。编辑:
MySQL时间戳不会在内部存储为字符串,但是当从数据库中提取它们时,它们会被转换为字符串。 DATETIME
类型不会被MySQL
修改,这意味着如果您将日期放入数据库中,则会得到相同的日期。
如果您的网站有来自不同时区的访问者,您需要将日期转换为字符串而不是整数。在一些国家,日期不仅仅是数字(例如在法国,日期格式为“Mardi 15 mai 2012”)。我更喜欢使用PHP或JS进行转换,因为我认为简单的整数转字符串比整数转字符串再转字符串要快。此外,如果迁移到其他国家的服务器,也不会出现任何问题。
虚假缺点:
unsigned
整数是int(10)
,意味着您可以存储高达4294967295
的数字,但它的限制并不固定,因此我们可以轻松地将int(10)
int
更改为int(16)
bigint
date_add(my_date, interval 2 months)
应该只被计算一次,但无论如何都可以在 PHP 端完成。关于时区的事情我不确定... MySQL 时间戳是否嵌入了时区或者是全局的数据库设置? - mpenUNIX_TIMESTAMP()
似乎不能处理负数(至少不是没有一些技巧)。 - mpen这还不错,但您将失去一些内置功能,例如:
select * from table1 where dateColumn = getDate()-30
如果可以,请使用datetime!