有一个问题,是否可能有一个时间戳列在更新一列(仅更新该列)时更新?
提前感谢!
您可以使用触发器:
DELIMITER ;;
CREATE TRIGGER foo BEFORE UPDATE ON my_table FOR EACH ROW
IF NEW.colA <=> OLD.colA -- has not been updated
AND NEW.colB <=> OLD.colB -- has not been updated
AND NOT NEW.colC <=> OLD.colC -- has been updated
-- etc.
THEN
SET NEW.colD = NOW(); -- update timestamp
END IF
;;
DELIMITER ;
请注意,您的TIMESTAMP
列没有启用自动更新。
当然。您可以将时间戳列定义为日期时间,然后在满足条件时使用触发器更新它。
大致语法为:
CREATE TRIGGER trigger_name AFTER UPDATE ON table FOR EACH ROW
BEGIN
IF(
NOT NEW.watched_column <=> OLD.watched_column AND
NEW.other_a <=> NEW.other_a AND
...
)
THEN
SET NEW.timestamp_column = NOW();
END IF;
END