MySql 更新时间戳损坏

Question

更新包含时间戳的表时出现奇怪的结果，有人知道为什么会这样吗？还有其他解决方案吗？我的代码是

SET SQL_SAFE_UPDATES = 0;

create temporary table _peters
(
    order_time TIMESTAMP,
    pickup_time TIMESTAMP,
    item_count INT DEFAULT 0
);

insert _peters (order_time, pickup_time)
      select '2018-10-20T09:00:00.000', '2018-10-20T09:00:00.000'
union select '2018-10-20T10:00:00.000', '2018-10-20T10:00:00.000'
union select '2018-10-20T11:00:00.000', '2018-10-20T11:00:00.000';

update _peters p,
(
    select '2018-10-20T10:00:00.000' as order_time,
           '2018-10-20T10:00:00.000' as pickup_time,
           10 as item_count
) z
set p.item_count = z.item_count, p.pickup_time = z.pickup_time
where p.order_time = z.order_time
and p.pickup_time = z.pickup_time;

select * from _peters;
drop temporary table _peters;

注意

set p.item_count = z.item_count, p.pickup_time = z.pickup_time

如果没有“p.pickup_time = z.pickup_time”，目标时间戳就会损坏，如这些结果所示。

Result of select * from _peters;

任何想法 - 或者这是一个错误？谢谢。

Answer 1

这是因为您将字段设置为 TIMESTAMP -- 如果您使用了 DATETIME，您可以定义任何日期/时间并且它不应该被覆盖。 TIMESTAMP 默认为 CURRENT_TIMESTAMP

create temporary table _peters
(
    order_time DATETIME,
    pickup_time DATETIME,
    item_count INT DEFAULT 0
);