使用 MySQL 的 TIMESTAMP 与直接存储时间戳
我对于以 MySQL 的 TIMESTAMP 格式保存日期和时间值与以自定义 UNSIGNED INT 格式保存日期和时间值感到左右为难。这里主要考虑的是检索速度、PHP 中适当的范围计算以及偶尔格式化为人类可读值。
每种类型所需的存储空间及其范围：
DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
UNSIGNED INT 4 bytes (Maximum Value 4294967295)
我根本不需要DATETIME的范围。我在 TIMESTAMP 和 UNSIGNED INT 之间左右为难。
支持 UNSIGNED INT 的论点：
- UNIX 时间戳 4294967295 转换为 Sun, 07 Feb 2106 06:28:15 GMT，这比 TIMESTAMP 更好，对我来说足够好
- 直接在 PHP 中比较这些时间戳会比通过 strtotime 转换 TIMESTAMP 更快() 然后比较它们
TIMESTAMP 给我的唯一优势是当我从手动修改 mysql 表并需要“查看”它们。
是否有任何令人信服的理由使用 TIMESTAMP 而不是 UNSIGNED INT？
I'm in a dilemma about saving date and time values in MySQL's TIMESTAMP format vs in a custom UNSIGNED INT format. The main considerations here are speed of retrieval, appropriate range calculations in PHP and occasional formatting into human readable values.
The storage space required for each type and their ranges:
DATETIME 8 bytes '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP 4 bytes '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
UNSIGNED INT 4 bytes (Maximum Value 4294967295)
I dont need the range of DATETIME at all. I'm torn between TIMESTAMP and UNSIGNED INT.
Arguments in favor of UNSIGNED INT:
- A UNIX timestamp of 4294967295 converts to Sun, 07 Feb 2106 06:28:15 GMT which is more than TIMESTAMP and good enough for me
- Comparing these timestamps directly in PHP would be faster rather than converting TIMESTAMPs via strtotime() and then comparing them
The only advantage TIMESTAMP would give me is when I'm reading in the values from the mysql table manually and need to 'see' them.
Is there any compelling reason to use TIMESTAMP and not an UNSIGNED INT?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
TIMESTAMP 的参数
DEFAULT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP拥有自动时间戳列（MySQL 5.6.5 之前每个表仅一列）FROM_UNIXTIME()函数 - 这将使编写可以使用索引的查询变得更容易在 PHP 中
<前><代码>>>日期('Ymd h:i:s',4294967295);
'1969-12-31 11:59:59'
所以范围实际上是相同的
Arguments for TIMESTAMP
DEFAULT CURRENT_TIMESTAMPorON UPDATE CURRENT_TIMESTAMP(one column per table only until MySQL 5.6.5)FROM_UNIXTIME()function - it will make it easier to write queries that can use indexesIn PHP
so the range is in fact the same
TIMESTAMP 的唯一真正用途是当您希望在更新行时自动更新该字段（这是该字段的默认行为），或者当数据存储要求非常严格以至于每行 4 个字节确实会产生影响时你。
实际上，比较应该在 DATETIME 和 UNSIGNED INT 之间进行，我推荐 DATETIME 因为：
SELECT UNIX_TIMESTAMP(field) FROM table，无需选择原始值并使用 strtotime，第二点确实消除了以整数存储的任何理由。
The only real use for TIMESTAMP is when you want that field to be updated automatically when the row is updated (which is the default behaviour for that field), or when data storage requirements are so strict that 4 bytes per row really makes a difference to you.
Really the comparison should be between DATETIME and UNSIGNED INT, and I'd recommend DATETIME because:
SELECT UNIX_TIMESTAMP(field) FROM table, no need to select out the raw value and use strtotimePoint two alone really removes any reason to store in integers, in my opinion.
这可能不是一个“科学”的答案，但我总是发现 MySql 在 TIMESTAMP 列上处理转换、算术、比较等的方式令人困惑。 UNSIGNED INT 列更加直接，我总是知道会发生什么。
PS 也许支持 TIMESTAMP 列的另一件事是它能够在每次更新或插入后自动设置为当前时间，但这并不是您不能没有的东西。
This might not be a "scientific" answer but I always find the way MySql handles conversion, arithmetics, comparsion, etc... on TIMESTAMP columns confusing. An UNSIGNED INT column is much more straight forward and I always know what to expect.
P.S. Perhaps one other thing in favor of TIMESTAMP column is its ability to be automatically set to current time after each update or insert but that is not something you can't live without.
一如既往，这取决于您需要保存什么。
例如，如果您正在使用某些 API 中的数据，并且它以数字形式向您发送时间（秒，这在市场数据中很常见），那么将其存储为 unsigned int 而不是每次都转换为字符串会更容易、更快在插入之前。
As always it depends on what you need to save.
For example if you are consuming data from some API and it sends to you the time as a number (seconds, this is common in market data) then could be easier and faster just to store it as unsigned int instead of converting everytime to a string before inserting it.