php - 如果有多个字段，则重复键更新

Question

INSERT INTO highscore(user, points, modality, time, level)
VALUES('$user', '$score', '$modality', '$time', '$level')

ON DUPLICATE KEY UPDATE
  points = IF(VALUES(points) > points, VALUES(points), points),
  time = IF(VALUES(points) > points, VALUES(time), time),
  level = IF(VALUES(points) > points, VALUES(level), level)

唯一字段是“用户”和“模式”

此代码不起作用，如果 old_points > new_points 此查询仅更新点字段。当新点 > 旧点时，我必须用各自的值更新这 3 个字段。

如何解决？

Answer 1

用这个

points = IF(VALUES(points) > points, VALUES(points), points),
time = IF(VALUES(points) >= points, VALUES(time), time),
level = IF(VALUES(points) >= points, VALUES(level), level)

注意>=符号

解释:第一行中points的值已经改变