INSERT ... ON DUPLICATE KEY UPDATE

╰半夏微凉° 2023-06-19 04:20 17阅读 0赞

网上关于INSERT … ON DUPLICATE KEY UPDATE大多数文章都是同一篇文章转来转去，首先这个语法的目的是为了解决重复性，当数据库中存在某个记录时，执行这条语句会更新它，而不存在这条记录时，会插入它。

相当于 先判断一条记录是否存在，存在则update，否则insert。其语法是：

INSERT INTO tablename(field1,field2, field3, ...) VALUES(value1, value2, value3, ...) ON DUPLICATE KEY UPDATE field1=value1,field2=value2, field3=value3, ...;
    tablename是表名，field1，field2，field3等是字段名称，value1，value2，value3等是字段值。

例如：

INSERT INTO t_stock_chg(f_market, f_stockID, f_name) VALUES('SH', '600000', '白云机场') ON DUPLICATE KEY UPDATE f_market='SH', f_name='浦发银行';

这条语句如果记录不存在，插入该记录；存在将f\_market改为SH，f\_name改为浦发银行。现在有个问题是：这条语句判断该条记录是否存在的标准是什么？由于同一个值是可以同时出现在多个记录中的，所以必须有个字段是唯一不能重复的。

规则是这样的：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。所以 ON DUPLICATE KEY UPDATE是不能写where条件的，例如如下语法是错误的：

INSERT INTO t_stock_chg(f_market, f_stockID, f_name) VALUES('SH', '600000', '白云机场') ON DUPLICATE KEY UPDATE f_market='SH', f_name='浦发银行' WHERE f_stockID='600000';

因为由UNIQUE索引或者主键保证唯一性，不需要WHERE子条件。所以上面的INSERT INTO t\_stock\_chg(f\_market, f\_stockID, f\_name) VALUES(‘SH’, ‘600000’, ‘白云机场’) ON DUPLICATE KEY UPDATE f\_market=‘SH’, f\_name=‘浦发银行’;中f\_stockID就是唯一主键。

这里特别需要注意的是：如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2，如果更新的数据和已有的数据一模一样，则受影响的行数是0，这意味着不会去更新，也就是说即使你有的时间戳是自动记录最后一次的更新时间，这个时间戳也不会变动。例如：

CREATE TABLE `t_stock_chg` (
    `f_market` varchar(64) NOT NULL COMMENT '市场',
    `f_stockID` varchar(10) NOT NULL DEFAULT '' COMMENT '股票代码',
    `f_updatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入时间戳',
    `f_name` varchar(16) DEFAULT NULL COMMENT '股票名称',
    PRIMARY KEY (`f_market`,`f_stockID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

这里的字段f\_updatetime每次在更新数据时会自动更新，但是如果记录中存在某条数据，后来又更新它，而更新的数据和原数据一模一样，那么这个字段也不会更新，仍然是上一次的时间。此时INSERT … ON DUPLICATE KEY UPDATE影响行数是0。

在对一对多属性修改时我们可能会常常用到该功能，不用 专门业务代码去判断是新增还是修改；

-- 一个活动可能对应多个商品的多个SKU，可能一个商品有多个SKU但是只要其中几个SKU参加活动
    create table jiuyoulife_uat.j_shop_activity_goods
    (
       group_id             national int(11) not null comment '团购活动ID',
       product_id           national char(32) not null comment '产品ID',
       goods_id             national char(32) not null comment 'goods_id',
       activity_type        int(2) not null default NULL comment '活动类型:0团购',
       stock                int(11) comment '库存',
       super_price          double comment '活动价格',
       sales                int(11) comment '销售数量',
       settle_type          int(2) default 0 comment '结算方式:0按比例结算1按底价结算',
       ratio                double comment '底价:浮点数;结算比例(1-100)%',
       create_time          datetime default CURRENT_TIMESTAMP,
       update_time          datetime,
       primary key (group_id, goods_id)
    );
    
    
    -- 插入更新:如果三个主键相同则更新，否则进行插入操作
    insert into j_shop_activity_goods ( group_id, product_id, goods_id, activity_type, stock, super_price, sales, settle_type, ratio, create_time, update_time) values 
    ( 1, 1, 1, 0, 100,0.01, 0, 0, 10,NOW(),NOW() ) , 
    ( 1, 1, 1, 0, 100,0.01, 0, 0, 10,NOW(),NOW() ) 
     ON DUPLICATE KEY UPDATE 
     activity_type = VALUES(activity_type), 
     stock = VALUES(stock), 
     super_price = VALUES(super_price), 
     settle_type = VALUES(settle_type),
      ratio = VALUES(ratio), 
      update_time = VALUES(update_time)

## 参考文 ##

前半部分原文出自：  
沫小淘https://www.cnblogs.com/moxiaotao/p/9431808.html