大数据比对批量操作之mysql ON DUPLICATE KEY UPDATE使用

应用场景：MySQL数据量达到百万级别，并且数据更新时大部分数据重复，需要比对更新或者插入新的数据
效果：MySQL执行过程中如果数据库中存在该记录则执行对应更新操作，不存在执行插入操作，而且这些操作是在数据库引擎中完成；避免了对数据进行批量操作时，首先对重复数据进行过滤，然后进行CRUD操作，减少对数据库访问压力


语法：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]


       [INTO] tbl_name [(col_name,...)]


       VALUES ({expr | DEFAULT},...),(...),...


       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


或：


INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]


       [INTO] tbl_name


       SET col_name={expr | DEFAULT}, ...


       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]


或：


INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]


       [INTO] tbl_name [(col_name,...)]


       SELECT ...


       [ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 


如果指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：


mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)  ON DUPLICATE KEY UPDATE c=c+1; 
mysql> UPDATE table SET c=c+1 WHERE a=1;
如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。 


注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：


mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。


您可以在UPDATE子句中使用VALUES(col_name)函数从INSERT...UPDATE语句的INSERT部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE子句中的VALUES(col_name)可以引用被插入的col_name的值。本函数特别适用于多行插入。VALUES()函数只在INSERT...UPDATE语句中有意义，其它时候会返回NULL。 


示例：


mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
本语句与以下两个语句作用相同：


mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) ON DUPLICATE KEY UPDATE c=9;
当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。


如果更新记录时，更关心记录中的某一个或多个属性，建议使用索引，将关心的字段是设置为索引，创建多个属性唯一索引方法如下：


CREATE TABLE `mobilebasestation` (
  `id` int(10)  NOT NULL AUTO_INCREMENT,
  `mobileNetworkCode` varchar(50) DEFAULT NULL ,
  `mobileCountryCode` varchar(50) DEFAULT NULL ,
  `cellId` int(5) DEFAULT '0' COMMENT ,
  `locationAreaCode` int(5) DEFAULT '0',
  `baiduAreaId` int(5) DEFAULT NULL COMMENT,
PRIMARY KEY (`id`),
  UNIQUE INDEX  `UNIQ_ID` (`mobileNetworkCode`,`mobileCountryCode`,`cellId`,`locationAreaCode`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


但是，经实际验证，如果联合索引属性过多，会导致查询效率直线下降，因此如果必须涉及多个属性的情况下，建议增加一个属性字段作为其他几个属性的唯一索引