on duplicate key update 的使用

作用

  on duplicate key update 是 MySQL 的特有语法，作用就是：当表中没有记录时就插入，有的话就更新。这个有没有记录的依据就是主键或唯一索引，插入数据的主键或唯一索引在表中存在就执行更新，否则执行插入。

基本用法

1.新建机构表，其中 id 为自增主键，tjcode 为唯一索引。

DROP TABLE IF EXISTS `tjinfo_test`;
CREATE TABLE `tjinfo_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '物理主键',
  `tjcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '机构代码',
  `tjname` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '机构名称',
  `tjlevel` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '机构层级',
  `status` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '机构状态',
  `utjcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL COMMENT '上级机构',
  `created` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `modified` datetime(0) NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uk_tjcode`(`tjcode`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '机构表' ROW_FORMAT = Dynamic;

2.插入数据。

insert into tjinfo_test(tjcode, tjname, tjlevel, status, utjcode, created) values ('00000', '中国总公司', '1', '0', '0', now());

3.执行以下 sql：

insert into tjinfo_test(tjcode, tjname, tjlevel, status, utjcode, created) values ('00001', '河南省公司', '2', '0', '00000', now()) 
on duplicate key update tjname = '河南省公司1', tjlevel = '2', status = '0', utjcode = '00000', modified = now();

因为 tjcode 为 00001 的数据不存在，所以执行了插入 values 后面的数据。

4.再次执行上面这个 sql。

会发现 tjname 和 modified 变了，因为 tjcode 为 00001 的数据存在，所以对 on duplicate key update 后面声明的字段进行了更新。

5.执行以下 sql：

insert into tjinfo_test(tjcode, tjname, tjlevel, status, utjcode, created) values ('00001', '河南省公司1', '2', '0', '00000', now()) 
on duplicate key update tjname = values(tjname), tjlevel = values(tjlevel), status = values(status), utjcode = values(utjcode), modified = now();

发现和上面的 sql 效果一样，tjname = values(tjname) 中 values 的作用就是将前面声明的，想要插入的 values()中的 tjname 字段拿过来。如果将 tjname = values(tjname) 修改为 tjname = tjname 则会将该字段保持为原有值，不进行更新。这三种写法可以根据不同业务场景使用。

进阶用法

仅作格式示范，不作讲解。

1.insert into...select...on duplicate key update：

insert into tjinfo_test(tjcode, tjname, created) select tmptjcode, tmptjname, now() from tjinfo_tmp
on duplicate key update tjname = tmptjname, modified = now();

2.加上 left join：

insert into cpic_ddzs_tjcode
    (tjcode, tjname, tjlevel, status, utjcode, created)
select tmp.tmptjcode, tmp.tmptjname, tmp.tjlevel, tmp.status, tmp.utjcode, now() from
    (select t1.tmptjcode, t1.tmptjname, t1.tjlevel, t1.status, t2.utjcode
        from tjinfo_tmp t1
        left join rlship_tmp t2 on t1.tmptjcode = t2.tmptjcode) as tmp
on duplicate key update
    tjname = tmp.tmptjname, tjlevel = tmp.tjlevel, status = tmp.status, 
    utjcode = tmp.utjcode, modified = now()

3.加上 case when：

insert into cpic_ddzs_tjcode
    (tjcode, tjname, tjlevel, status, utjcode, created)
select tmp.tmptjcode, tmp.tmptjname, tmp.tjlevel, tmp.status, tmp.utjcode, now() from
    (select t1.tmptjcode, t1.tmptjname, t2.utjcode,
        case when t1.tjlevel = 'A' then '1' when t1.tjlevel = 'B' then '2' when t1.tjlevel = 'C' then '3' else '4' end tjlevel,
        case when t1.status = '01' then '0' else '2' end status
    from tjinfo_tmp t1
    left join rlship_tmp t2 on t1.tmptjcode = t2.tmptjcode) as tmp
on duplicate key update
    tjname = tmp.tmptjname, tjlevel = tmp.tjlevel, status = tmp.status, 
    utjcode = tmp.utjcode, modified = now()