mysql update 加if_MYSQL INSERT或UPDATE IF

首先,在名称,条形码,项目,位置和价格上添加UNIQUE约束.

ALTER TABLE tableX

ADD CONSTRAINT tableX_UQ

UNIQUE (name, barcode, item, location, price) ;

然后你可以使用INSERT INTO … ON DUPLICATE KEY UPDATE：

INSERT INTO tableX

(name, barcode, item, location, price, quantity, date)

VALUES

(?, ?, ?, ?, ?, ?, ?)

ON DUPLICATE KEY UPDATE

quantity = CASE WHEN VALUES(date) > date

THEN quantity + VALUES(quantity) -- add quantity

ELSE quantity -- or leave as it is

END

, date = CASE WHEN VALUES(date) > date

THEN VALUES(date) ; -- set date to new date

ELSE date -- or leave as it is

END

也可以使用REPLACE,但行为存在差异(如果你有外键,这尤其重要).有关详细信息,请参阅此问题“INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”和@Bill Kawin的答案,其中讨论了INSERT IGNORE,INSERT … ON DUPLICATE KEY和REPLACE之间的区别.