MySQL-insert-or-update

发表于 | 分类于

我们经常会遇到类似的业务场景，插入一条数据如果他不存在则执行 insert ，当这条记录存在的时候，我们去 update 他的一些属性（或者什么都不做）。

解决方案：

  • 使用 ON DUPLICATE KEY UPDATE
    在 主键 或者 唯一约束 重复时，执行更新操作。
  • 使用 REPLACE INTO
    在 主键 或者 唯一约束 重复时，先 delete 再 insert。

ON DUPLICATE KEY UPDATE

  • 创建表，建立唯一约束，准备一条数据
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TABLE `stu_class_ref` (
      `id` varchar(30) NOT NULL,
      `stu_id` varchar(30) DEFAULT NULL,
      `class_id` varchar(30) DEFAULT NULL,
      `note` varchar(100) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `stu_id` (`stu_id`,`class_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    INSERT INTO 
    `test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
    VALUES ('001', 'zhangsan', 'yuwen', NULL);

准备数据

  • 使用 ON DUPLICATE KEY UPDATE

    1
    2
    3
    4
    5
    6
    INSERT INTO 
    `test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
    VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', '我喜欢语文:)')
    ON DUPLICATE KEY UPDATE note = '我喜欢语文:)'
    > Affected rows: 2
    > 时间: 0.042s

  • Affected rows: 2，MySQL 检查插入的行是否会产生重复键错误，如果会则执行update

ON DUPLICATE KEY UPDATE

  • 如果想要引用 VALUES 中的值，参考如下
    1
    2
    3
    4
    5
    6
    INSERT INTO 
    `test`.`stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
    VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
    ON DUPLICATE KEY UPDATE note = VALUES(class_id)
    > Affected rows: 2
    > 时间: 0.006s

ON DUPLICATE KEY UPDATE

REPLACE INTO

  • MySQL 中 还有一个黑科技语法 REPLACE INTO
    1
    2
    3
    4
    REPLACE INTO `stu_class_ref`(`id`, `stu_id`, `class_id`, `note`) 
    VALUES (UUID_SHORT(), 'zhangsan', 'yuwen', NULL)
    > Affected rows: 2
    > 时间: 0.004s

REPLACE INTO

  • REPLACE INTO 就比较简单粗暴了，他会先执行delete 操作，然后insert

ON DUPLICATE KEY UPDATE 与 REPLACE INTO

  • 再来创建一张表, 创建三个唯一约束, 插入三条数据
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    CREATE TABLE `interesting` (
      `id` varchar(30) NOT NULL,
      `uni_a` varchar(30) DEFAULT NULL,
      `uni_b` varchar(30) DEFAULT NULL,
      `uni_c` varchar(30) DEFAULT NULL,
      `version` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `uni_a` (`uni_a`) USING BTREE,
      UNIQUE KEY `uni_b` (`uni_b`) USING BTREE,
      UNIQUE KEY `uni_c` (`uni_c`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
    VALUES ('1', 'a', 'a', 'a', NULL);
    INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
    VALUES ('2', 'b', 'b', 'b', NULL);
    INSERT INTO `test`.`interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
    VALUES ('3', 'c', 'c', 'c', NULL);

准备数据

  1. 执行 ON DUPLICATE KEY UPDATE
    1
    2
    3
    4
    5
    INSERT INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
    VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
    ON DUPLICATE KEY UPDATE version = 666
    > Affected rows: 2
    > 时间: 0.049s

ON DUPLICATE KEY UPDATE

  • Affected rows: 2 但是其实三条主键都有冲突了
  1. 再看一下 REPLACE INTO
    1
    2
    3
    4
    REPLACE INTO `interesting`(`id`, `uni_a`, `uni_b`, `uni_c`, `version`) 
    VALUES (UUID_SHORT(), 'a', 'b', 'c', NULL)
    > Affected rows: 4
    > 时间: 0.026s

REPLACE INTO

  • Affected rows: 4 REPLACE INTO 将三条有冲突的全部delete 然后 insert

####总结：

  • ON DUPLICATE KEY UPDATE 只会对所匹配的第一行进行update,
  • REPLACE INTO 会对所有匹配行进行delete, insert
  • 所以应避免对有多个唯一索引的表使用