检查是否存在，如果存在，则以1 ++更新，如果不插入 [英] Check if exists, if so, update by 1++, if not insert

问题描述

大家好，我目前有一个插入语句 $query= "INSERT into new_mail VALUES ('$to1', '0')"; ，其中字段是用户名和message_number 目前，我要检查条目是否存在，是执行选择查询，然后使用mysql_num_rows(php)检查行数.如果rows == 1，那么我得到当前的 message_number 并将其设置为

Hey guys quick question, I currently have an insert statement $query= "INSERT into new_mail VALUES ('$to1', '0')"; where fields are username, and message_number Currently what I would do to check if the entry exists, is do a select query then check the number of rows with mysql_num_rows (php). If rows==1 then I get the current message_number and set it equal to

$row['message_number']+1. 

然后我用另一个查询更新该条目.

Then I update that entry with another query.

是否只有一种查询(仅检查是否存在，是否不插入，如果更新，则更新message_number，加1)就可以在mysql中完成所有这些操作?

Is there an easier way to do all this in just mysql with just one query (check if exists, if not insert, if so update message_number, increase by 1)?

推荐答案

使用 INSERT...ON DUPLICATE KEY UPDATE . MySQL手册中的示例几乎可以完全满足您的需求:

Use INSERT...ON DUPLICATE KEY UPDATE. The MySQL manual has an example which does almost exactly what you need:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

要执行此操作，您需要在用于检查重复项的列上添加UNIQUE索引.不过，有一个重要警告:

To make this work you need to add a UNIQUE index on the column that you use to check for duplicates. There is one important warning though:

通常，您应该尝试避免在具有多个唯一索引的表上使用ON DUPLICATE KEY子句.

In general, you should try to avoid using an ON DUPLICATE KEY clause on tables with multiple unique indexes.

这篇关于检查是否存在，如果存在，则以1 ++更新，如果不插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！