MySQL_插入更新 ON DUPLICATE KEY UPDATE

平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件.

此时 插入数据的时候 ，经常会有这样的情况：

我们想向数据库插入一条记录：

  若数据表中存在以相同主键的记录，我们就更新该条记录。

  否则就插入一条新的记录。

逻辑上我们需要怎么写：

$result = mysql_query('select * from xxx where id = 1');
$row = mysql_fetch_assoc($result);
if($row){
mysql_query('update ...');
}else{
mysql_query('insert ...');
}

但是这样写有两个问题

1、效率太差，每次执行都要执行2个sql
2、高并发的情况下数据会出问题，不能保证原子性 

还好MySQL 为我们解决了这个问题：我们可以通过 ON DUPLICATE KEY UPDATE  达到以上目的, 且能保证操作的原子性和数据的完整性。

ON DUPLICATE KEY UPDATE 可以达到以下目的:

向数据库中插入一条记录：

若该数据的主键值/ UNIQUE KEY 已经在表中存在,则执行更新操作, 即UPDATE 后面的操作。

否则插入一条新的记录。

示例：

Step1 . 创建表，插入测试数

-- ----------------------------
-- Table structure for mRowUpdate
-- ----------------------------
DROP TABLE IF EXISTS `mRowUpdate`;CREATE TABLE `eip_test` (
  `Domain` varchar(255) NOT NULL,
  `StartIP` varchar(255) DEFAULT NULL,
  `EndIP` varchar(255) DEFAULT NULL,
  `StartPort` varchar(255) DEFAULT NULL,
  `EndPort` varchar(255) DEFAULT NULL,
  `Command` int(10) DEFAULT NULL,
  PRIMARY KEY (`Domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- ----------------------------
-- Records of mRowUpdate
-- ----------------------------
INSERT INTO `eip_test` VALUES('aa','aa','aa','aa','a',1)
INSERT INTO `eip_test` VALUES('qq','qq','qq','qq','q',1)

 

Step2 .测试 ON DUPLICATE KEY UPDATE 的使用方法：

INSERT INTO `eip_test` VALUES('aa','aa','aa','aa','a',1)ON DUPLICATE KEY UPDATE `StartIP`='SuperMan';

Step3. 查询数据的变化情况

你也可以这个样子写：

INSERT INTO `eip_test` VALUES('aa','aa','aa','aa','a',1)ON DUPLICATE KEY UPDATE `StartIP` =VALUES(`Domain`);

数据又改变回来了

==========================     话外篇   ===============================

技巧:

技巧 1 :

ON DUPLICATE KEY UPDATE 特别适用于多行插入。如:

INSERT INTO `table` (`a`, `b`, `c`) VALUES (1, 2, 3), (4, 5, 6) ON DUPLICATE KEY UPDATE `c`=VALUES(`a`)+VALUES(`b`);

          Tips: VALUES()函数只在INSERT…UPDATE语句中有意义，其它时候会返回NULL。

注意事项:

注意 1:

若多个索引都冲突，则只有一条记录被修改。

create table test(
id int not null primary key,
num int not null UNIQUE key,
tid int not null
)
为了测试两个唯一索引都冲突的情况，然后插入下面的数据

insert into test values(1,1,1), (2,2,2);
然后执行：

insert into test values(1,2,3) on duplicate key update tid = tid + 1;
因为a和b都是唯一索引，插入的数据在两条记录上产生了冲突，然而执行后只有第一条记录被修改