在副本上更新和增加多个列

Question

我有以下查询插入多个值和重复需要更新列

INSERT INTO stock (price, stock1, stock2) VALUES
('99', '10', 0), 
('120', 0, '10')
ON DUPLICATE KEY UPDATE  
stock1 = COALESCE(stock1 + VALUES(stock1), stock1), 
`stock1-C` = `stock1-C` + (VALUES(stock1) IS NOT NULL),
stock2 = COALESCE(stock2 + VALUES(stock2), stock2), 
`stock2-C` = `stock2-C` + (VALUES(stock2) IS NOT NULL)

第一次运行这个查询应该添加(+)columncolumn-C值与1，让我用一些例子来解释

下面是默认表，其中price是唯一列，其余列的默认值为0 stock2-C

Answer 1

由于列不可为空，您必须为不想在INSERT语句中提供值的列传递0s而不是nulls，并使用与0而不是null的比较:

INSERT INTO stock (price, stock1, stock2) VALUES
('99', '10', 0), 
('120', 0, '12')
ON DUPLICATE KEY UPDATE 
`stock1-C` = `stock1-C` + (VALUES(stock1) <> 0 AND stock1 <> 0),
stock1 = stock1 + VALUES(stock1), 
`stock2-C` = `stock2-C` + (VALUES(stock2) <> 0 AND stock2 <> 0),
stock2 = stock2 + VALUES(stock2)

结果:

<表类>价格stock1stock1-Cstock2stock2-Ctbody><<tr>991000012000120