php - SQL : How to overwrite entry only if new one is higher? 否则创建新条目

Question

我有一个名为“highscore”的表，如下所示:
nameQL分数QL
彼得 50

并将带有 NAME 和 SCORE 的 flash 游戏导出到具有此名称的 PHP。

如何在 PHP 文件中实现:

• 如果(NAME 存在于数据库中(nameQL)AND SCORE> this.name.scoreQL){Raplace scoreQL用 SCORE WHERE nameQL=NAME}
• IF(名称不存在){创建带有 NAME 和 SCORE 的新行)

Answer 1

我会使用 insert .. on duplicate key update ... 语句。像这样:

insert into highscore set
    name = :name,
    score = :new_score
on duplicate key update
    score = greatest(score, :new_score)

name 列应该被索引为unique。

测试脚本:

create table player (
    name varchar(32) primary key,
    score int not null default 0
);

-- create new players
insert into player set name = 'foo', score = 100
    on duplicate key update score = greatest(score, 100);
insert into player set name = 'bar', score = 100
    on duplicate key update score = greatest(score, 100);
insert into player set name = 'baz', score = 100
    on duplicate key update score = greatest(score, 100);

-- update score of existing player
insert into player set name = 'bar', score = 200
    on duplicate key update score = greatest(score, 200);

select * from player 的输出:

+------+-------+
| name | score |
+------+-------+
| bar  |   200 |
| baz  |   100 |
| foo  |   100 |
+------+-------+