INSERT INTO 或 UPDATE 有两个条件 [英] INSERT INTO or UPDATE with two conditions

问题描述

这个问题乍一看似乎很简单，但我只是没有找到一个合理的时间明智的解决方案.

This problem seems easy at first sight, but I simply have not found a solution that is reasonable time wise.

考虑具有以下特征的表格:

Consider a table with the following characteristics:

ID INTEGER PRIMARY KEY AUTOINCREMENT
name INTEGER
values1 INTEGER
values2 INTEGER
dates DATE

每天都会生成 N 个新行，用于未来的日期，并且名称"来自有限列表.我想在有新数据时插入一个新行，但如果已经有一行带有名称"和日期"，只需更新它.

Every day, N amount of new rows are generated, for dates into the future, and with the 'name' coming from a finite list. I would like to insert a new row when there is new data, but if there is already a row with 'name' and 'dates', simply update it.

请注意，当前提议的检查条件的 SPROC 解决方案是不可行的，因为这是从另一种语言推送的数据.

Please note that a current proposed solution of an SPROC that checks the conditional is not feasible, as this is data being pushed from another language.

推荐答案

这就是insert on duplicate key update 的用途.

它的手册页是这里.

诀窍是该表需要有一个唯一的键(可以是复合键)，以便可以检测到执行插入的冲突.因此，更新要发生在该行上，否则是插入.当然，它可以是主键.

The trick is that the table needs to have a unique key (can be a composite) so that the clash of doing an insert can be detected. As such, the update to occur on that row, otherwise an insert. It can be a primary key, of course.

在你的情况下，你可以有一个复合键，例如

In your case, you could have a composite key such as

unique key(theName,theDate)

如果该行已经存在，则检测到 clash，并进行更新.

If the row is already there, the clash is detected, and the update happens.

create table myThing
(   id int auto_increment primary key,
    name int not null,
    values1 int not null,
    values2 int not null,
    dates date not null,
    unique key(name,dates) -- <---- this line here is darn important
);

insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (778,1,1,'2015-07-11') on duplicate key update values2=values2+1;
-- do the 1st one a few more times:
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;
insert myThing(name,values1,values2,dates) values (777,1,1,'2015-07-11') on duplicate key update values2=values2+1;

显示结果

select * from myThing;
+----+------+---------+---------+------------+
| id | name | values1 | values2 | dates      |
+----+------+---------+---------+------------+
|  1 |  777 |       1 |       4 | 2015-07-11 |
|  2 |  778 |       1 |       1 | 2015-07-11 |
+----+------+---------+---------+------------+

正如预期的那样，插入重复的键更新有效，只有 2 行.

As expected, insert on duplicate key update works, just 2 rows.

这篇关于INSERT INTO 或 UPDATE 有两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！