MySQL-更新(如果存在)，否则用两个键插入 [英] MySQL -- Update if exists else insert with two keys

问题描述

我有一个表，其字段为foreign_key_id | value1 | value2，如果我有对Foreign_key_id和value1的匹配项，我想更新value2.

I have a table with fields foreign_key_id | value1 | value2, and I want to update value2 if I have a match for foreign_key_id and value1.

如果foreign_key_id或value1不存在，我想插入一个新行.有没有比PHP使用select语句然后跳转到更新或插入分支更好的方法了?

If foreign_key_id or value1 do not exist, I want to insert a new row. Is there a better way to do this than having PHP use a select statement and then branching to either an update or insert?

value2可以与数据库中的值相同，因此我无法运行和更新，看看受影响的行是否为0，如果是，则运行并插入.

value2 can be the same value as in the database, so I cannot run and update, see if affected_rows is 0, and run and insert if it is.

推荐答案

尝试使用IF EXISTS确定是执行UPDATE还是INSERT语句.您可以在一个PHP语句/查询中执行此操作.

Try using an IF EXISTS to determine whether to execute an UPDATE or an INSERT statement. You can do this in one PHP statement/query.

IF EXISTS(SELECT 1 FROM Mytable WHERE foreign_key_id = f1 AND value1 = v1)
BEGIN
    UPDATE Mytable SET value2 = v2
    WHERE foreign_key_id = f1 AND value1 = v1;
END
ELSE
BEGIN
      INSERT INTO Mytable(foreign_key_id,value1,value2)
      VALUES (f1,v1,v2);
END IF;

这篇关于MySQL-更新(如果存在)，否则用两个键插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！