MySQL的日期时间和时间戳字段是否更适合PHP应用程序，然后是Unix时间戳内联？ [英] Are MySQL datetime and timestamp fields better for PHP apps then Unix timestamp ints?
问题描述
我正在阅读一篇文章，该文章显示了三个不同的MySQL日期/时间存储选项执行情况的一些非常好的信息和基准。
I was reading over an article that shows some really good information and benchmarks about how well the three different MySQL date/time storage options perform.
MySQL DATETIME vs TIMESTAMP vs INT性能和使用MyISAM进行基准测试
在阅读文章时，您开始意识到使用整数只是一种浪费而你应该去使用MySQL Datetime或Timestamp列类型。
While reading the article you start to get the idea that using ints are just a waste and you should instead go with MySQL Datetime or Timestamp column types.
然而，在文章的最后，他再做一次不使用MySQL函数的测试，你突然发现直接的INT是通过unix时间戳搜索时，两个MySQL选项的速度提高了2倍。
However, towards the end of the article he does one more test not using MySQL functions and you suddenly see that straight INT's are 2x as fast as the two MySQL options when searching by unix timestamps.
所以它突然恍然大悟 - duh，PHP应用程序是什么所有使用？ time（）！几乎每个php应用程序都将其逻辑基于Unix Epoch。这意味着大多数查询在特定时间内的结果都是基于time（）开始的，然后转换为使用MySQL的字段。
So it suddenly dawned on me - duh, what do PHP apps all use? time()! Almost every php application bases their logic off of the Unix Epoch. Which means that most queries for results in a certain time start off based on time() and then are converted to work with MySQL's fields.
这给我留下了以下内容：
This leaves me with the following:
-
Unix存储为INT的时间戳更快，
，占用更少的空间，并且工作
本地使用PHP的time（）基于
计算。
Unix Timestamps stored as INT's are faster, take less space, and work natively with PHP's time() based calculations.
MySQL日期类型更适合
操作和逻辑来自MySQL
方。
MySQL Date types are more suited to operations and logic from the MySQL side.
目前无论是Unix还是
MySQL时间戳只能工作到
2037，这意味着你必须在
的未来使用
日期时间字段表示更大的日期。
For the time being both Unix And MySQL Timestamps only work until 2037 which means that you must use a datetime field for larger dates in the future.
MySQL命令如 date =当可能会滞后。
使用复制导致数据不一致时，NOW（）
MySQL commands like date = NOW() can lag when
using replication causing data inconsistencies.
所以将它应用到现实生活中我们看到回答
这些结果给出了大多数真正的DBA会使用像PostgreSQL这样的更好的引擎 - 是否存在
So applying this to real life we see that answer that these results given that most really DBA's would use a better engine like PostgreSQL - is there arny
然而，大多数使用数据库逻辑的应用程序可能会使用PostgreSQL。这意味着我们所有其他程序员只使用MySQL来为我们的数据存储（你知道这是真的）这使得字段保持小，快，UNIX INT似乎实际上是最好的选择。
However, most apps that would be to the level of using DB logic would probably go with PostgreSQL. Which means that all the rest of us programmers only use MySQL for a storage tank for our data (you know it's true) which makes keeping the fields as small, fast, UNIX INT's seem like it is actually the best option.
那你们觉得怎么样？
时间戳是否真的比MySQL日期字段更适合PHP应用程序？
推荐答案
MySQL的日期格式没有2038年的问题。
MySQL的日期从1000年起是可靠的到了9999年，而Unix时间戳可能会在2038年之后或1902之前搞砸，除非系统中的所有内容都是64位。
MySQL's dates are reliable from the year 1000 to the year 9999, whereas Unix timestamps can screw up after 2038 or before 1902 unless everything in your system is 64-bit.
如果您使用的是PHP，那么，这可能没有实际意义：PHP在其大多数日期和时间函数中使用unix时间戳记来表示日期和时间，除非你使用的是64位版本，否则它将具有相同的限制。
If you're using PHP, however, this can be moot: PHP uses unix timestamps for dates and times throughout most of its date and time functions and unless you are using a 64-bit build it will have the same limitation.
您将使用专门用于此目的的字段类型。
如果您关心。将日期作为unix时间戳放入INT字段不是自描述的;如果不以适当的方式转换数据，则无法查看数据。但这可能对你没什么影响。
If you care. Putting date into an INT field as a unix timestamp is not as self-describing; you can't look at the data without converting it in the appropriate way. But that may make no difference to you.
考虑到你正在使用PHP，这方面的另一面是，一旦你有时间进入PHP，你就会不管怎样，必须将它转换回Unix时间戳才能对它做任何有用的事情，因为对于PHP，Unix时间戳是原生的。
The flip side of this, given that you're using PHP, is that once you get the time into PHP you'd have to convert it back to a Unix timestamp anyway to do anything useful with it, because to PHP, Unix timestamps are native.
编辑：
当我写这个答案时，我没有使用PHP的DateTime类。使用DateTime类可以消除使用Unix时间戳的任何需要，并消除32/64位问题。感谢查尔斯在下面的评论，指出了一种使用它的好方法。
Back when I wrote this answer, I didn't use PHP's DateTime class. Using the DateTime class removes any need to use Unix timestamps, and removes the 32-/64-bit issues. Thanks to Charles' comment below for pointing out a good way to use this.
这篇关于MySQL的日期时间和时间戳字段是否更适合PHP应用程序，然后是Unix时间戳内联？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
