我有一张表，在这张表中，我有一列当前存储 BigInt 类型的值，我想更改此列以包含“时间戳”值。
我怎样才能做到这一点。我试过了:
ALTER TABLE t
MODIFY c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
我得到:
#1292 - Incorrect datetime value: '1524120113' for column 'created_temp' at row 1
和
ALTER TABLE share ADD created_temp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
UPDATE share SET created_temp = UNIX_TIMESTAMP(created);
我得到:
#1292 - Incorrect datetime value: '1524120113'
created 的内容是使用
UNIX_TIMESTAMP() 创建的
请您参考如下方法:
您遇到第一个错误是因为 DBMS 无法即时转换列。第二个错误来自这样一个事实，即 UNIX_TIMESTAMP() 需要一个 DATE 、 TIME 或 TIMESTAMP 字符串而不是整数，所以你应该使用 FROM_UNIXTIME() 。UNIX_TIMESTAMP() 文档对这两个函数的区别有一个重要的说明:
If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values.
——
这是您完成任务所需的步骤( 可能需要很长时间才能完成，因为必须重写所有行! ):
ALTER TABLE mytable ADD new_column TIMESTAMP NULL;
-- PS: You might want to use a default value instead of NULL here.
UPDATE mytable SET new_column = FROM_UNIXTIME(old_column) WHERE old_column IS NOT NULL;
ALTER TABLE mytable DROP COLUMN old_column;
ALTER TABLE mytable CHANGE new_column old_column;
