MySQL列类型"TIMESTAMP"隐式地包括“在更新CURRENT_TIMESTAMP上的NOT NULL DEFAULT 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
)
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.
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.