on duplicate key update 的使用

作用

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

基本用法

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

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()