MySQL、phpMyAdmin：TIMESTAMP 始终执行 NOW 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6576604/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL, phpMyAdmin: TIMESTAMP Always Executes NOW Function
提问by dcolumbus
There's something very irritating going on with my TIMESTAMPS ...
我的时间戳有一些非常令人恼火的事情......
I have a "createdat", "deletedat" and "updatedat" column in my Table... I've set my deletedat and updated at to NULL and DEFAULT NULL ... however, when a new record is added, the NOW() function is always executed for deletedat and updatedat instead of just leaving it as NULL.
我的表中有一个“createdat”，“deletedat”和“updatedat”列......我已经将我的deletedat和updatedat设置为NULL和DEFAULT NULL......但是，当添加新记录时，NOW（ ) 函数总是针对 deleteat 和 updatedat 执行，而不是仅仅将其保留为 NULL。
So I end up with: 00:00:00 ...
所以我最终得到： 00:00:00 ...
Why isn't it just defaulting to NULL?
为什么不只是默认为 NULL？
Here's my Table:
这是我的表：
Here's when Inserting (notice the NOW function is selected):
这是插入时（注意选择了 NOW 功能）：
The following SQL is executed:
执行以下 SQL：
INSERT INTO `MYTABLE_DEV`.`messages` (`id`, `fromUserId`, `toUserId`, `subject`, `body`, `createdat`, `updatedat`, `deletedat`) VALUES (NULL, '1', '3', 'Message', 'This is another message.', CURRENT_TIMESTAMP, NOW(), NOW());
采纳答案by Bohemian
This is expected behaviour.
这是预期的行为。
Unlike other databases, in MySQL TIMESTAMPcolumns are ALWAYSupdated with now()whenever the row is updated. This is a deliberate feature of the TIMESTAMP datatype.
不像其他的数据库，在MySQLTIMESTAMP列总是与更新now()每当行被更新。这是 TIMESTAMP 数据类型的一个有意特性。
Edit: Note that I am talking here about TIMESTAMP, notTIMESTAMP DEFAULT NULLor any other variations.
编辑：请注意，我在这里谈论的是TIMESTAMP，而不是TIMESTAMP DEFAULT NULL任何其他变体。
What you want is a DATETIMEdatatype - they behave as normal columns.
你想要的是一种DATETIME数据类型——它们的行为与普通列一样。
Here's some test SQL to show its behaviour:
这是一些测试 SQL 以显示其行为：
create table timestamp_datatype (id int, dt datetime, ts timestamp);
-- test 1: leaving ts to default - you get now()
insert into timestamp_datatype (id, dt) values (1, '2011-01-01 01:01:01');
-- test 2: trying to give ts a value - this works
insert into timestamp_datatype (id, dt, ts) values (2, '2011-01-01 01:01:01', '2011-01-01 01:01:01');
-- test 3: specifying null for ts - this doesn't work - you get now()
insert into timestamp_datatype (id, dt, ts) values (3, '2011-01-01 01:01:01', null);
-- test 4: updating the row - ts is updated too
insert into timestamp_datatype (id, dt, ts) values (4, '2011-01-01 01:01:01', '2011-01-01 01:01:01');
update timestamp_datatype set dt = now() where id = 4; -- ts is updated to now()
select * from timestamp_datatype;
+------+---------------------+---------------------+
| id | dt | ts |
+------+---------------------+---------------------+
| 1 | 2011-01-01 01:01:01 | 2011-07-05 09:50:24 |
| 2 | 2011-01-01 01:01:01 | 2011-01-01 01:01:01 |
| 3 | 2011-01-01 01:01:01 | 2011-07-05 09:50:24 |
| 4 | 2011-07-05 09:50:24 | 2011-07-05 09:50:24 |
+------+---------------------+---------------------+
回答by Brian Graham
You are executing NOW() instead of setting null. Use this query:
您正在执行 NOW() 而不是设置 null。使用此查询：
INSERT INTO `MYTABLE_DEV`.`messages` (`id`, `fromUserId`, `toUserId`, `subject`, `body`) VALUES (NULL, '1', '3', 'Message', 'This is another message.');
or with all fields...
或所有领域...
INSERT INTO `MYTABLE_DEV`.`messages` (`id`, `fromUserId`, `toUserId`, `subject`, `body`, `createdat`, `updatedat`, `deletedat`) VALUES (NULL, '1', '3', 'Message', 'This is another message.', CURRENT_TIMESTAMP, NULL, NULL);
回答by Andrey Nagikh
Let it be here (little patch upcoming in 3.5.2 which compleatly solves this problem): the bug report
让它在这里（3.5.2 中即将推出的小补丁完美地解决了这个问题）： 错误报告
回答by Viktor
This might be a bug/feature in phpMyAdmin. Have a look at this bug report, and especially the comment added at 2010-06-13 11:06:47 UTC. The interesting part:
这可能是 phpMyAdmin 中的一个错误/功能。看看这个错误报告，特别是在 2010-06-13 11:06:47 UTC 添加的评论。有趣的部分：
You can question however, the default value of NOW() in the selectionbox when doing an INSERT/UPDATE, when the default value of the timestamp field is set to NULL. This is maybe not what you would suspect when setting the default to NULL.
但是，当时间戳字段的默认值设置为 NULL 时，您可以质疑在执行 INSERT/UPDATE 时选择框中 NOW() 的默认值。在将默认值设置为 NULL 时，这可能不是您所怀疑的。
If this is true, I guess that's just the way phpMyAdmin works. I don't use phpMyAdmin myself, so I have no personal experience from this.
如果这是真的，我想这就是 phpMyAdmin 的工作方式。我自己不使用 phpMyAdmin，所以我没有这方面的个人经验。
