不使用重复键MYSQL进行Upsert(Upsert without using on duplicate key MYSQL)

不使用重复键MYSQL进行Upsert(Upsert without using on duplicate key MYSQL)

我正在尝试编写一个mysql语句，无需使用主键即可将其插入表中。

我知道on duplicate key命令，但我不能在这里使用它，因为我正在检查两列不是主键的唯一性。 我知道最好只生成这两个主键，但我不能，因为这是给出的模式。

架构如下所示：

tbl_order_detail

key_order_detail key_order key_product some_other_keys

如果key_order，key_product对是唯一的，那么我会进行常规插入。 如果它们不是唯一的，那么我更新行。

有什么建议？

I'm trying to write a mysql statement that upserts into a table without having to use the primary key.

I know of the on duplicate key command but I can't use it here since I'm checking for the uniqueness of two columns that aren't primary keys. I know it would be better to just make these two primary keys, but I can't since this was the schema that was given.

The schema looks like this:

tbl_order_detail

key_order_detail key_order key_product some_other_keys

If the key_order,key_product pair is unique then I do a regular insert. If they aren't unique then I update the row.

Any suggestions?

最满意答案

ON DUPLICATE KEY UPDATE不仅适用于主键，还适用于任何唯一约束。

因此，只需为(key_order, key_product)列创建复合唯一索引并使用它。

ON DUPLICATE KEY UPDATE works not only with primary keys, but with any unique constraints.

So just create a composite unique index for (key_order, key_product) columns and use it.