如何增量更新timestamp字段 实现now()逐行递增1秒的更新效果

你之前的UPDATE语句所有行拿到相同时间，是因为now()（以及绝大多数SQL的当前时间函数）在单个SQL语句执行周期内只会计算一次，不会逐行重新取值。

MySQL / MariaDB 写法 #

用用户自定义变量记录偏移量，逐行递增：

-- 初始化偏移量，第一行加0秒，所以初始设为-1
SET @time_offset = -1;

UPDATE some_table
SET `timestamp` = DATE_ADD(NOW(), INTERVAL (@time_offset := @time_offset + 1) SECOND)
-- 确保按id顺序分配偏移量，可根据需求修改排序规则
ORDER BY id ASC;

如果需要指定固定起始时间，把NOW()替换成对应的时间值即可，比如'2021-08-17 04:14:00'。

PostgreSQL 写法 #

用窗口函数生成行偏移量再关联更新：

WITH offset_mapping AS (
  SELECT 
    id,
    -- 行号从1开始，减1后第一行偏移为0秒
    (ROW_NUMBER() OVER (ORDER BY id ASC) - 1) AS second_offset
  FROM some_table
)
UPDATE some_table t
SET `timestamp` = NOW() + (offset_mapping.second_offset || ' SECOND')::INTERVAL
FROM offset_mapping
WHERE t.id = offset_mapping.id;

执行UPDATE前建议先运行查询校验预期结果，避免误改数据，以MySQL为例：

SET @time_offset = -1;
SELECT 
  id,
  `timestamp` AS original_time,
  DATE_ADD(NOW(), INTERVAL (@time_offset := @time_offset + 1) SECOND) AS expected_time
FROM some_table
ORDER BY id ASC;

确认expected_time符合需求后再执行更新操作。

内容的提问来源于stack exchange，提问作者Ted