大家好,我目前有一个插入语句 $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 ++更新,如果不插入
发布评论