MySQL如何根据其是否存在来插入新记录或更新字段? [英] MySQL How to insert new record or update a field depending on whether it exists?

问题描述

我正在尝试实施一个评分系统，该数据库将以下两个字段保留在我的数据库表中:

I am trying to implement a rating system where I keep the following two fields in my db table:

等级(当前等级) num_rates(到目前为止已提交的评分数)

rating (the current rating) num_rates (the number of ratings submitted so far)

UPDATE `mytable`
   SET rating=((rating*num_rates)+$theRating)/num_rates, num_rates=num_rates+1
 WHERE uniqueCol='$uniqueCol'

变量来自我的PHP代码.

the variables are from my PHP code.

因此，基本上，有时在数据库中不存在带有uniqueCol的行，因此，如果存在，我该如何做上述声明，如果不存在，则该做以下事情:

So, basically sometimes the row with the uniqueCol does not exist in the DB, so how can I do the above statement if the exists and if it doesn't then do something like this:

INSERT INTO `mytable`
   SET rating=$theRating, num_rates=1, uniqueCol=$uniqueCol

推荐答案

看看它看起来应该像这样:

INSERT INTO mytable (rating, num_rates, uniqueCol)
VALUES ($theRating, 1, $uniqueCol)
ON DUPLICATE KEY UPDATE
  rating=((rating*num_rates)+$theRating)/num_rates,
  num_rates=num_rates+1;

确保uniqueCol上有UNIQUE index或PRIMARY KEY.

这篇关于MySQL如何根据其是否存在来插入新记录或更新字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！