526互联

mysql的ON DUPLICATE KEY的用法

发布时间 2023-09-12 11:02:24作者: zslm___
1. 直接更改字段值 
Insert into table(code,name)
values('a','aa')
ON DUPLICATE KEY
update updateTime=now()

2. 根据原值修改当前值

传入参数 #{step}

INSERT INTO  table(code,version) 
VALUES ('a',(@newVersion:=#{step})) 
ON DUPLICATE KEY UPDATE 
version=(@newVersion:=version+#{step});

3. 批量修改

-- bean_name为唯一字段        
INSERT INTO job (bean_name,params,cron_expression)
        select  bean_name,params,cron_expression
        from m_job
        where  id=#{dtmJobId}
        ON DUPLICATE KEY UPDATE
        cron_expression=values(cron_expression),
        params=values(params)