MySQL如何根据其是否存在来插入新记录或更新字段?

本文介绍了MySQL如何根据其是否存在来插入新记录或更新字段?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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.