MSQL：如何覆盖条目只有新的更高？否则创建新的条目 [英] MSQL: How to overwrite entry only if new one is higher? else create new entry

问题描述

我有一个名为highscore的表，像这样：

nameQL scoreQL

piotr 50

和flash游戏与NAME和SCORE导出到PHP这个名字。

如何在PHP文件中做到这一点：

• IF（名称存在于数据库（nameQL）
  AND SCORE> this.name.scoreQL）{Raplace scoreQL
  with SCORE WHERE nameQL = NAME}


• 如果（NAME不存在）{使用NAME和SCORE创建
  新行）
  
  
  

  插入..在重复键更新... 语句。就像这样：
  
  pre $ insert into highscore set
name =：name，
score =：new_score $

分数=最大（分数，：new_score）


  name 列应该被索引为 unique 。

  
  
  

  测试脚本：
  
  

    create table player（
   name varchar（32）主键
   score int not null default 0 
  ）; 
   
    - 创建新玩家
  插入玩家集名称='foo'，得分= 100 
  ，重复密钥更新得分=最大（得分，100）; 
  插入到玩家集名称='bar'中，得分= 100 
  ，重复键更新得分=最大（得分，100）; 
  插入到玩家套装名称='baz'，分数= 100 
  上重复键更新分数=最大（分数，100）; 
   
    - 现有玩家的更新分数
  插入玩家套装名称='bar'，分数= 200 
  重复密钥更新分数=最大（分数，200）; 
    

  输出 select * from player ：

    + ------ + ------- + 
   |名字|分数| 
   + ------ + ------- + 
   |酒吧| 200 | 
   | baz | 100 | 
   | foo | 100 | 
   + ------ + ------- + 
    

  
  

  I have table named "highscore" like this:
  nameQL scoreQL
  piotr 50

  And flash game with NAME and SCORE exported to PHP with this names.

  How to make this in PHP file:

  • IF (NAME exists in database (nameQL) AND SCORE> this.name.scoreQL){Raplace scoreQL with SCORE WHERE nameQL=NAME}
  • IF (NAME doesn't exists){Create new row with NAME and SCORE)

  解决方案

  I would use insert .. on duplicate key update ... statement. Something like this:

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

  name column should be indexed as unique.

  Test script:

  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);
  

  Output of select * from player:

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

  这篇关于MSQL：如何覆盖条目只有新的更高？否则创建新的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！