1. MySQL 批量插入
有的时候会有这样的需求:
- 批量写入数据,但是数据可能存在主键或者唯一性索引的冲突写到一半就失败了。
- 每天批量更新表,新数据插入,旧数据更新。
于是我们不禁想到是否能实现一个 insertOrUpdate
方法呢?事实上,MySQL 给出了标准 SQL 的两种扩展来帮我们实现这种需求。
2. INSERT ... ON DUPLICATE KEY UPDATE
标准 SQL 的扩展,如果插入时遇到主键冲突或者唯一性约束不满足则会执行 update
操作,例如:
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
支持多行插入:
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
在 ON DUPLICATE KEY UPDATE
子句中的 VALUES(a)
表示的是如果没有冲突将会插入 a
列的值,即 1 或者 4。
ON DUPLICATE KEY UPDATE
子句不适宜在有多个唯一性索引的表上使用。
参考:14.2.5.3 INSERT … ON DUPLICATE KEY UPDATE Syntax
3. Replace
Replace
是标准 SQL 的扩展,等价于 inserts/deletes and inserts
。
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
SET col_name={expr | DEFAULT}, ...
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(col_name,...)]
SELECT ...
所有列的值都从 Replace
语句中获取,无法从现有的行中获取值,如果有的列没有指定值那么会给定默认值。如果在指定值的时候使用了 SET col_name = col_name + 1
语句,等价于 SET col_name = DEFAULT(col_name) + 1
。
Replace
语句的返回值表示影响的行数,影响行数 = 删除的行数 + 插入的行数。对于单行的 Replace
操作,如果返回 1 表示仅仅插入了新的一行,如果大于 1 则表示删除了至少一行并插入了一行。
3. 异同分析
从文档的说明来看,至少有两点差异:
INSERT … ON DUPLICATE KEY UPDATE
在插入时可以使用现有行的值,Replace
不支持。mysql root@localhost:test> create table tb_table (a int not NULL PRIMARY key auto_increment, b int NOT NULL DEFAULT 0, c int NOT NULL DEFAULT 0) Query OK, 0 rows affected Time: 0.042s mysql root@localhost:test> insert into tb_table (a, b, c) VALUES (1,2,3); Query OK, 1 row affected Time: 0.024s mysql root@localhost:test> INSERT INTO tb_table (a, b, c) values (1,5,6) on DUPLICATE KEY UPDATE b = values(b) + b; Query OK, 2 rows affected Time: 0.025s mysql root@localhost:test> SELECT * from tb_table; +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 1 | 7 | 3 | +-----+-----+-----+ 1 row in set Time: 0.001s
INSERT … ON DUPLICATE KEY UPDATE
在不满足主键约束或者唯一性约束的时候是更新操作,所以主键不会变。而Replace
是deletes inserts
也就是说老的记录会被删除,如果不指定主键值那么可能会破坏已有的外键关联(比如主键是自增产生,插入的时候不指定主键值)。mysql root@localhost:test> create table tb_table (a int not NULL PRIMARY key auto_increment, b int NOT NULL DEFAULT 0 unique, c int NOT NULL DEFAULT 0) Query OK, 0 rows affected Time: 0.027s mysql root@localhost:test> show create table tb_table; +----------+----------------+ | Table | Create Table | |----------+----------------| | tb_table | CREATE TABLE `tb_table` ( `a` int(11) NOT NULL AUTO_INCREMENT, `b` int(11) NOT NULL DEFAULT '0', `c` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +----------+----------------+ 1 row in set Time: 0.020s mysql root@localhost:test> insert into tb_table (b,c) VALUES (2,3); Query OK, 1 row affected Time: 0.013s mysql root@localhost:test> SELECT * from tb_table; +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 1 | 2 | 3 | +-----+-----+-----+ 1 row in set Time: 0.001s mysql root@localhost:test> replace into tb_table (b,c) values (2,4); Query OK, 2 rows affected Time: 0.023s mysql root@localhost:test> SELECT * from tb_table; +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 2 | 2 | 4 | +-----+-----+-----+ 1 row in set Time: 0.001s mysql root@localhost:test> replace into tb_table (b,c) values (2,4),(3,5); Query OK, 3 rows affected Time: 0.007s mysql root@localhost:test> SELECT * from tb_table; +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 3 | 2 | 4 | | 4 | 3 | 5 | +-----+-----+-----+ 2 rows in set Time: 0.001s mysql root@localhost:test> insert into tb_table (b,c) values (2,8) on DUPLICATE KEY UPDATE c = VALUES(c); Query OK, 2 rows affected Time: 0.013s mysql root@localhost:test> SELECT * from tb_table; +-----+-----+-----+ | a | b | c | |-----+-----+-----| | 3 | 2 | 8 | | 4 | 3 | 5 | +-----+-----+-----+ 2 rows in set Time: 0.001s