mysql批量插入on duplicate key update

        最近项目中遇到这么个需求，往数据表中批量插入一批数据，由于唯一索引，导致表中某条数据可能已经存在。

        虽然在插入之前可以先用 select 判断数据是否存在，如果不存在就 insert，否则 update。这样对于每条数据都得至少两次连库操作，效率较低。

        并且，在批量插入之前，可能数据并不存在，但是在批量插入过程中，唯一索引会导致部分数据即使有两条也只会插入一条。而事实上，我们希望看到的是，第一条插入后，当第二条再插入时，根据唯一索引该条记录就变成 update 而不是仅仅 insert 失败或者忽略，所以也不能加关键字 ignore。

        这里 mysql 关键字 on duplicate key update 就可以完美地实现这部分需求。实验如下，先看下表结构：

CREATE TABLE `test_grade` (
    `id` BIGINT(11) UNSIGNED NOT NULL AUTO_INCREMENT,                                                     
    `name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '姓名',                                                         
    `course` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '课程',
    `score` SMALLINT(2) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分数',
    PRIMARY `test_grade`KEY (`id`),
    UNIQUE KEY `idx_name_course` (`name`,`course`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

        列 name 和 course 构成唯一索引。目前表中数据如下：

mysql> select * from test_grade;
+----+-------+--------+-------+
| id | name  | course | score |
+----+-------+--------+-------+
|  1 | bob   | 数学   |    82 |
|  2 | allen | 语文   |    76 |
+----+-------+--------+-------+
2 rows in set (0.00 sec)

        此时如果直接执行下面语句：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('bob', '数学', 95);
ERROR 1062 (23000): Duplicate entry 'bob-数学' for key 'idx_name_course'

        加上关键字 on duplicate key update 试试：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('bob', '数学', 95) on duplicate key update score = values(score);
Query OK, 2 rows affected (0.09 sec)
mysql>
mysql> select * from test_grade;
+----+-------+--------+-------+
| id | name  | course | score |
+----+-------+--------+-------+
|  1 | bob   | 数学   |    95 |
|  2 | allen | 语文   |    76 |
+----+-------+--------+-------+
2 rows in set (0.00 sec)

        尽管唯一索引已经存在，但仍然执行成功，score 值也更新为95。如果同时插入两条相同唯一索引数据呢：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('jack', '物理', 88), ('jack', '物理', 85);
ERROR 1062 (23000): Duplicate entry 'jack-物理' for key 'idx_name_course'
mysql>
mysql> select * from test_grade;
+----+-------+--------+-------+
| id | name  | course | score |
+----+-------+--------+-------+
|  1 | bob   | 数学   |    93 |
|  2 | allen | 语文   |    76 |
+----+-------+--------+-------+
2 rows in set (0.00 sec)

        可以看到两条都 insert 失败，加上 on duplicate key update 试试：

mysql> INSERT INTO test_grade(NAME, course, score) VALUES('jack', '物理', 88), ('jack', '物理', 85) on duplicate key update score = values(score);
Query OK, 3 rows affected (0.36 sec)
Records: 2  Duplicates: 1  Warnings: 0

mysql> select * from test_grade;
+----+-------+--------+-------+
| id | name  | course | score |
+----+-------+--------+-------+
|  1 | bob   | 数学   |    93 |
|  2 | allen | 语文   |    76 |
|  9 | jack  | 物理   |    85 |
+----+-------+--------+-------+
3 rows in set (0.00 sec)

        可以看到 insert 成功了，并有提示重复数为1，而且 score 值也是最后一次插入时的值。

        至此，我们的需求就得以实现。不过也得注意到，此时主键 id 并不是连续增长的了。