MySQL unix 时间戳应该如何存储在 int 列中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4289793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How should unix timestamps be stored in int columns?
提问by Xeoncross
I have a logging table that will contain millions of writes for statistical reasons. All the columns are int foreign keys. I am also going to add a timestamp column for each row. Given that DATETIME takes 8bits - I will be using int(10) unsignedto cut the storage space (and index on that column) in half.
由于统计原因，我有一个日志记录表，其中包含数百万次写入。所有列都是 int 外键。我还将为每一行添加一个时间戳列。鉴于 DATETIME 需要 8 位 - 我将使用int(10) unsigned将存储空间（和该列上的索引）减半。
However, I'm wondering when this column would no longer work. At 3:14:07AM on 19th January 2038 the value 9,999,999,999 will be a problem for UNIX timestamps - but an unsigned int in MySQL only holds up to 4,294,967,295 and the timestamp 4294967295 is showing an invalid number in my PHP application.
但是，我想知道这个专栏何时不再有效。在 2038 年 1 月 19 日凌晨 3:14:07，值 9,999,999,999 将成为 UNIX 时间戳的问题 - 但 MySQL 中的 unsigned int 最多只能容纳 4,294,967,295，而时间戳 4294967295 在我的 PHP 应用程序中显示无效数字。
So what does this mean? Is the end of the storing int timestamps in MySQL going to be sometime in 2021 since it can't make it all the way to 9999999999?
那么这是什么意思？在 MySQL 中存储 int 时间戳是否会在 2021 年某个时候结束，因为它无法一直到 9999999999？
Answer:
回答：
- 2147483647 is 2038 (not 9999999999) so there is no problem.
unsignedisn't needed since 2147483647 fits fine in a signed MySQL int.
- 2147483647 是 2038（不是 9999999999）所以没有问题。
unsigned不需要，因为 2147483647 很适合带符号的 MySQL int。
回答by Marc B
Standard UNIX timestamps are a signed 32bit integer, which in MySQL is a regular "int" column. There's no way you could store 9,999,999,999, as that's way outside the representation range - the highest a 32bit int of any sort can go is 4,294,967,295. The highest a signed 32bit in goes is 2,147,483,647.
标准 UNIX 时间戳是一个有符号的 32 位整数，在 MySQL 中是一个常规的“int”列。您无法存储 9,999,999,999，因为这超出了表示范围 - 任何类型的 32 位 int 的最高值是 4,294,967,295。最高的有符号 32 位是 2,147,483,647。
If/when UNIX timestamps go to a 64bit data type, then you'll have to use a MySQL "bigint" to store them.
如果/当 UNIX 时间戳变为 64 位数据类型时，则必须使用 MySQL“bigint”来存储它们。
As for int(10), the (10)portion is merely for display purposes. MySQL will still use a full 32bit internally to store the number, but only display 10 whenever you do a select on the table.
至于int(10)，该(10)部分仅用于展示目的。MySQL 仍将在内部使用完整的 32 位来存储数字，但在您对表进行选择时仅显示 10。
