发布于 由admin

Insert Or Update

1. MySQL 批量插入

有的时候会有这样的需求：

  1. 批量写入数据，但是数据可能存在主键或者唯一性索引的冲突写到一半就失败了。
  2. 每天批量更新表，新数据插入，旧数据更新。

于是我们不禁想到是否能实现一个 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 则表示删除了至少一行并插入了一行。

参考：14.2.8 REPLACE Syntax

3. 异同分析

从文档的说明来看，至少有两点差异：

  1. 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
  2. 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

发表评论