mysql - 在重复键更新时添加值

Question

我有一个带有模式的表:

storeId varchar(255),
ttl int,
date varchar(255),
bytes bigint(255),
UNIQUE KEY storeId_date_index (storeId, date)

如果不存在，我想插入一行，否则更新它。

对于我点击的每个重复键，我想将旧值与新值相加。 ON DUPLICATE KEY UPDATE 命令如何做到这一点？

这是我目前所拥有的:

insert into table (storeId, date, ttl, bytes) 
values 
('477866', '2019-02-05', 54543543, 100),
('6301', '2019-02-05', 54543543, 999999),
('12345', '2019-02-05', 54543543, 999999)
ON DUPLICATE KEY UPDATE
bytes = oldval + newval # sum old value with new value where storeId and date match in the values

Answer 1

引用the documentation :

In assignment value expressions in the ON DUPLICATE KEY UPDATE clause, you can use the VALUES(col_name) function to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE statement.

即:

INSERT INTO mhytable(storeId, date, ttl, bytes) 
VALUES 
('477866', '2019-02-05', 54543543, 100),
('6301', '2019-02-05', 54543543, 999999),
('12345', '2019-02-05', 54543543, 999999)
ON DUPLICATE KEY UPDATE
bytes = bytes + VALUES(bytes)