如果两个字段存在则更新，如果不存在则插入(MySQL)

2022-01-09 00:00:00 insert mysql

这不是 这个问题 所以我开始了一个新的.

This isn't an (exact) duplicate of this questions so I'Ve started a new one.

我有这张表(ID 是主要的并且自动递增)

I have this table (ID is primary and auto increment)

ID | mykey | myfoo | mybar
============================
 1 | 1.1   | abc   | 123
 2 | 1.1.1 | def   | 456
 3 | 1.2   | abc   | 789
 4 | 1.1   | ghi   | 999

仅当 mykey = '1.1' AND myfoo = 'abc'

I would like to UPDATE row 1 with mybar = "333" only if mykey = '1.1' AND myfoo = 'abc'

如果 mykey != '1.1' OR myfoo != 'abc' 我想插入一个新行.

If either mykey != '1.1' OR myfoo != 'abc' I would like to INSERT an new row.

用一个语句就可以做到吗?

Is this possible with one statement?

推荐答案

MySQL 中的唯一索引不必位于单个列上.您只需在 ALTER TABLE..ADD UNIQUE 语句中指定更多列，即可在多个列上添加唯一索引:

A unique index in MySQL does not have to be on a single column. You can add a UNIQUE index on multiple columns simply by specifying more columns in your ALTER TABLE..ADD UNIQUE statement:

ALTER TABLE myTable ADD UNIQUE (
    mykey,
    myfoo
);

现在您可以使用常规的 INSERT INTO...ON DUPLICATE KEY 语句.

Now you can use a regular INSERT INTO...ON DUPLICATE KEY statement.

SQLFiddle 演示(注意没有添加多个重复值 - 所有其他值都是)

注意:

如果其中任何一个为 NULL，则它将不被视为唯一.mykey 是 'bar' 和 myfoo 是 NULL 可以添加到无穷大，即使它们具有相同"的值(NULL 并不是真正的值).

If either is NULL, it will not be counted as unique. mykey being 'bar' and myfoo being NULL could be added to infinity even though they have the "same" values (NULL isn't really a value).

相关文章