背景:
有表upsert_test,该表有a和b两列,其中a是该表的主键。如下图:
该表中现有两行数据:
现在使用向该表中插入数据:
insert into upsert_test values(2,1200),(3,1300);
结果是怎样?
好像会fail,但好像又不会完全fail。因为a是主键(主键是unique且not null),表中已经有(2,1100)这行数据了。插入(2,1200)这行数据时肯定会出错。但是(3,1300)这行数据好像能成功插入。
有的人可能会想:难道mysql不会根据主键来判断,主键重复就更新该行其他字段的值,不冲突就插入数据。想到挺好,但是目前没这么智能。再说要是这样的话还要update干嘛,一个insert不就能解决插入和更新操作了嘛。所以,这个操作会fail。
其实这种操作很常见,我们经常会把大量 的数据同时插入mysql。但是我们不知道这些数据中哪些主键是和表中数据的主键冲突。mysql提供了ON DUPLICATE KEY UPDATE语法来解决这个问题。
INSERT INTO upsert_test VALUES(2,1200),(3,1300)
ON DUPLICATE KEY UPDATE b = 1200;
如果主键不冲突就执行insert into操作,冲突就执行update操作。
但是如果同时插入几百条数据怎么办?UPDATE后面怎么写?
可以使用VALUES()函数。VALUES()函数只在INSERT…UPDATE语句中有意义,其它时候会返回NULL。
INSERT INTO upsert_test VALUES(2,1201),(3,1301),(4,1400)
ON DUPLICATE KEY UPDATE b = VALUES(b);
如果有多列呢?例如该表有a(主键),b和c 三个字段。
INSERT INTO upsert_test VALUES(2,1201),(3,1301),(4,1400)
ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c);
值得注意的是,若表中有多个unique约束的字段,结果就不一样了。
CREATE TABLE test(a INT PRIMARY KEY,
b INT UNIQUE KEY,
c INT);
插入两行数据:
INSERT INTO test VALUES(1,100,1000),(2,200,2000);
INSERT INTO test VALUES(1,200,3000)
ON DUPLICATE KEY UPDATE c=VALUES(c);
如此时执行上面这条sql,由于(1,200,3000)与第一行数据(1,100,1000)中的a字段重复,与第二行数据(2,200,2000)中的b字段重复,所以两行数据都会发生变化?
从结果来看,只有第一行数据c字段的发生了变化。