MySQL 列类型“TIMESTAMP"隐含地包括“NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 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 中有几个关于这个初始化的更新，你可以在这个 link(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，有一个参数叫做 explicit_defaults_for_timestamp 默认禁用.您可以启用此设置或将 DEFAULT 值设置为 0 或 NULL，与以前的 MySQL 版本相同.

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，那么 explicit_defaults_for_timestamp 默认启用.这会禁用非标准行为(谢天谢地).此外，当您禁用此设置时，MySQL 会生成警告.因此，例如，如果您没有为 TIMESTAMP 列定义 DEFAULT 值，它会自动设置为 NULL.

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"隐含地包括“NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！