问题描述
由于统计原因，我有一个记录表将包含数百万个写入。所有列都是int外键。我还要为每一行添加一个时间戳列。鉴于DATETIME需要8位，我将使用 int（10）unsigned 将存储空间（该列的索引）削减一半。
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) unsigned to cut the storage space (and index on that column) in half.
但是，我想知道这个列何时不再工作。在2038年1月19日上午3:14:07，值为9,999,999,999将是UNIX时间戳的问题 - 但MySQL中的unsigned int只能保存4,294,967,295，而时间戳4294967295在我的PHP应用程序中显示无效。
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.
那是什么意思？ MySQL的存储int时间戳的结束是在2021年的某个时候，因为它不能一直到9999999999？
答案：
- 2147483647是2038（不是9999999999），所以没有问题。
-
unsigned不需要，因为2147483647适用于已签名的MySQL int。
- 2147483647 is 2038 (not 9999999999) so there is no problem.
unsignedisn't needed since 2147483647 fits fine in a signed MySQL int.
推荐答案
标准UNIX时间戳是一个带符号的32位整数，在MySQL中是一个常规的int列。没有办法可以存储9,999,999,999，因为这种方式超出了表示范围 - 最高的32位int可以是4,294,967,295。签名32位的最高值为2,147,483,647。
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时间戳记转换为64位数据类型时，则必须使用MySQLbigint
If/when UNIX timestamps go to a 64bit data type, then you'll have to use a MySQL "bigint" to store them.
对于 int（10），（10） code>部分仅用于显示目的。 MySQL仍然会在内部使用一个完整的32bit来存储数字，但是只要您在表上进行选择，就会显示10。
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.
这篇关于unix时间戳应如何存储在int列中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！