MySql时间戳列自动更新。为什么?

I have a table created with this SQL:

我用这个SQL创建了一个表:

CREATE TABLE `test_table` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `data` TEXT,
  `timestamp` TIMESTAMP
) CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE InnoDB;

But when I run SHOW CREATE TABLE test_table, I get:

但是当我运行SHOW CREATE TABLE test_table时，我得到:

CREATE TABLE `test_table` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `data` text COLLATE utf8_unicode_ci,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Why the timestamp column being assigned ON UPDATE CURRENT_TIMESTAMP by default? I do not know how to get rid of this. Any help would be greatly appreciated.

为什么默认情况下在UPDATE CURRENT_TIMESTAMP上分配时间戳列?我不知道如何摆脱这个。如有任何帮助，我们将不胜感激。

2 个解决方案

#1

Use a datetime field instead

使用datetime字段

Should I use field 'datetime' or 'timestamp'?

我应该使用字段'datetime'还是'timestamp'?

Timestamps in MySQL generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

MySQL中的时间戳通常用于跟踪对记录的更改，并且常常在每次更改记录时进行更新。如果要存储特定的值，应该使用datetime字段。

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native format. You can do calculations within MySQL that way ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

如果您想要在使用UNIX时间戳还是使用本机MySQL datetime字段之间进行选择，请使用本机格式。您可以通过这种方式在MySQL中进行计算(“选择DATE_ADD(my_datetime, INTERVAL 1天)”)，如果您希望使用PHP对记录进行操作，那么在查询记录时，很容易将值的格式更改为UNIX时间戳(“选择UNIX_TIMESTAMP(my_datetime)”)。

#2

If you want that to your timestamp will be not added automatically ON UPDATE you have to define it like this

如果您希望在更新时不会自动地将其添加到您的时间戳中，您必须这样定义它

CREATE TABLE `test_table` (
   ...
   `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ...

#1