这个问题乍一看似乎很简单，但我只是没有找到一个合理的时间明智的解决方案.
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 有两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！