MySql TIMESTAMP列是自动更新的.为什么? [英] MySql TIMESTAMP column is auto updated. Why?

问题描述

我有一个用此SQL创建的表:

I have a table created with this 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;

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

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

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

为什么默认情况下将时间戳列分配为 ON UPDATE CURRENT_TIMESTAMP ?我不知道该如何摆脱.任何帮助将不胜感激.

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.

推荐答案

使用日期时间字段代替

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

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

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.

如果您要决定是使用UNIX时间戳还是 本机MySQL日期时间字段，请使用本机格式.你可以做 MySQL中的计算方式("SELECT DATE_ADD(my_datetime， INTERVAL 1 DAY))，更改值的格式很简单 到UNIX时间戳记("SELECT UNIX_TIMESTAMP(my_datetime)")时 如果要使用PHP对记录进行查询.

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.

这篇关于MySql TIMESTAMP列是自动更新的.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！