MySQL列类型"TIMESTAMP"隐式地包括“在更新CURRENT_TIMESTAMP上的NOT NULL DEFAULT CURRENT_TIMESTAMP". [英] MySQL column type "TIMESTAMP" implicitly includes "NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"

问题描述

我花了几个小时来追踪这个错误.鉴于以下SQL:

I've just spent a couple of hours tracking down this bug. Given the following SQL:

DROP DATABASE IF EXISTS db;
CREATE DATABASE db;
CREATE TABLE db.tbl (t1 TIMESTAMP) ENGINE=INNODB;
SHOW CREATE TABLE db.tbl;

最后一行显示给我:

'CREATE TABLE `tbl` (
  `t1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1'

NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP来自哪里?我没有写任何东西，而且我也不想要其中的任何一个，而我对于MySQL会做出这样的假设感到有些迷惑.

Where on earth does the NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP come from? I didn't write any of that, and I very much do not want any of that, and I'm kinda lost for words that MySQL would make such a presumption.

我是否打开/关闭了一些晦涩难懂的配置选项?这是默认行为吗?这是一个错误吗?无论如何，我该如何使MySQL表现出合理的行为?

Do I have some insane obscure configuration option turned on/off? Is this default behavior? It is a bug? In any case, how do I make MySQL behave sanely?

推荐答案

在MySQL 5.6.5 中，有一些有关此初始化的更新，您可以在

In MySQL 5.6.5 there are several updates regarding this initialization, you can see on this link (Automatic Timestamp Properties Before MySQL 5.6.5).

如果您使用的是MySQL< = 5.6.5 ，则要忽略此初始化，您需要将DEFAULT值设置为0或NULL，并允许NULL.

If you're using MySQL <= 5.6.5, in order to ignore this initialization you need to set the DEFAULT value to 0 or NULL with NULL allowed.

CREATE TABLE tbl
(
    field1 TIMESTAMP DEFAULT 0,
    field2 TIMESTAMP NULL DEFAULT NULL
)

如果您使用的是MySQL> = 5.6.6 ，则有一个名为

If you're using MySQL >= 5.6.6, there is parameter called explicit_defaults_for_timestamp which is disabled by default. You can enable this setting or set the DEFAULT value to 0 or NULL, same approach for previous MySQL versions.

如果您使用的是MySQL> = 8.0.2 ，则

If you're using MySQL >= 8.0.2, then explicit_defaults_for_timestamp is enabled by default. This disables the non-standard behaviour (thankfully). Also, MySQL generates a warning when you disable this setting. So, for instance, if you don't define DEFAULT value for a TIMESTAMP column, it is automatically set to NULL.

这篇关于MySQL列类型"TIMESTAMP"隐式地包括“在更新CURRENT_TIMESTAMP上的NOT NULL DEFAULT CURRENT_TIMESTAMP".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！