MySQL_插入更新 ON DUPLICATE KEY UPDATE

- 日理万妓 2022-05-15 01:41 319阅读 0赞

平时我们在设计数据库表的时候总会设计 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';

![Center][]

Step3. 查询数据的变化情况

![Center 1][]

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

**技巧:**

技巧 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都是唯一索引，插入的数据在两条记录上产生了冲突，然而执行后只有第一条记录被修改

[Center]: /images/20220515/75711374561f4b679ccb9e7dc56fbba7.png
[Center 1]: /images/20220515/e494b3fda2a44e799d0b028230376369.png