mysql主键重复,不抱错,只更新的骚操作 (如果没…

2018-06-22 05:39:23来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

平时我们在设计数据库表的时候总会设计 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 . 创建表,插入测试数据

 

SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------
-- Table structure for mRowUpdate
-- ----------------------------
DROP TABLE IF EXISTS `mRowUpdate`;
CREATE TABLE `mRowUpdate` (
  `id` int(11) NOT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;


-- ----------------------------
-- Records of mRowUpdate
-- ----------------------------
INSERT INTO `mRowUpdate` VALUES ('1', 'sss');
INSERT INTO `mRowUpdate` VALUES ('2', 'szh');
INSERT INTO `mRowUpdate` VALUES ('3', '9999');
SET FOREIGN_KEY_CHECKS=1;

 
 
 
Step2 .测试 ON DUPLICATE KEY UPDATE 的使用方法:
 
 
INSERT INTO mRowUpdate(id,`value`) VALUES(3, 'SuperMan') ON DUPLICATE KEY UPDATE `value`='SuperMan';
 
 
 
Step3. 查询数据的变化情况
select * from test.mRowUpdate;
 
id value
1 sss
2 szh
3 SuperMan
 
 
 
 
 
==========================     话外篇   ===============================
 
 
技巧:
 
技巧 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都是唯一索引,插入的数据在两条记录上产生了冲突,然而执行后只有第一条记录被修改
 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:PHP开发实用-阿里短信服务(Short Message Service)

下一篇:php的数组汉字符串常用函数