业务上经常存在一种现象，需要批量往表中插入多条数据，但在执行过程中，很可能因为唯一键冲突，而导致批量插入失败。

因此需要事先判断哪些数据是重复的，哪些是新增的。

比较常用的处理方法就是找出已存在的数据，并将其与不存在的数据区分开，已存在的数据一条条的更新。不存在的数据则批量更新。

这种方法会导致代码逻辑复杂，同时严重降低代码效率。

为了应对这种业务场景，MySQL有一种专有语法(insert into ... on duplicate key update)批量插入并更新唯一键数据

CREATE TABLE `user_card` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',

`uid` int(10) DEFAULT '' COMMENT '用户ID',

`grade_id` int(10) DEFAULT '' COMMENT '等级ID',

`name` varchar(255) DEFAULT '' COMMENT '姓名',

`money` decimal(10,2) DEFAULT '0.00' COMMENT '余额',

PRIMARY KEY (`id`),

UNIQUE KEY `uid_gid` (`uid`,`grade_id`) -- 业务上的唯一键

) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

如上图所示，批量插入的数据，遇到已存在记录(根据唯一键，创表语句中的 UNIQUE KEY 判断)时，自动更新已有的数据。

其中 money字段 以数据库现有值为准不进行更新， name字段使用了关键字 VALUES() 以外部值为准进行更新。

如果表中有多个唯一键(可以是单列索引或复合索引)，则任意一个唯一键(UNIQUE KEY)冲突时，都会自动更新数据。

通过 on duplicate key update 语法，可以指定哪些字段进行更新，哪些字段不进行更新。

所有操作均由SQL处理，不需要额外程序代码分析，能够大幅提高程序执行效率。

P.S：补充下不使用 replace into 语法的原因：

1、replace into 遇到已存在的记录，会先删除掉表中原有的记录后，再插入新的记录，

这样会导致该记录的主键发生变化，如果该表的主键和其它表有业务关联，那么会导致关联数据丢失。

2、replace into 插入的主键在某些时候不是连续自增的，这样会导致主键增长的数据很快，有时候会超过 int(10) 的最大值

mysql ON DUPLICATE KEY UPDATE重复插入时更新

mysql当插入重复时更新的方法: 第一种方法: 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: INSERT INTO clients (c ...

ON DUPLICATE KEY UPDATE重复插入时更新

mysql当插入重复时更新的方法: 第一种方法: 示例一:插入多条记录 假设有一个主键为 client_id 的 clients 表,可以使用下面的语句: INSERT INTO clients (c ...

mysql ON DUPLICATE KEY UPDATE 与 REPLACE INTO 的区别

#mysql ON DUPLICATE KEY UPDATE 如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY ...

mysql ON DUPLICATE KEY UPDATE ; 以及 同replace to 的区别.

需求: 1)如果admin表中没有数据, 插入一条 2)如果admin表中有数据, 不插入. 一般做法: if($result = mysql_query("select * from ad ...

深入mysql “ON DUPLICATE KEY UPDATE” 语法的分析

本篇文章是对mysql “ON DUPLICATE KEY UPDATE”语法进行了详细的分析介绍,需要的朋友参考下. mysql “ON DUPLICATE KEY UPDATE” 语法 如果在IN ...

MySQL: ON DUPLICATE KEY UPDATE 用法 避免重复插入数据

INSERT INTO osc_visit_stats(stat_date,type,id,view_count) VALUES (?,?,?,?) ON DUPLICATEKEY UPDATE vi ...

深入mysql "on duplicate key update" 语法的分析

如果在INSERT语句末尾指定了on duplicate key update,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不 ...

mysql ON DUPLICATE KEY UPDATE、REPLACE INTO

INSERT INTO ON DUPLICATE KEY UPDATE 与 REPLACE INTO,两个命令可以处理重复键值问题,在实际上它之间有什么区别呢?前提条件是这个表必须有一个唯一索引或主键 ...

mysql "ON DUPLICATE KEY UPDATE" 语法

如果在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE:如果不 ...

随机推荐

【React】启动dva脚手架

开始前: 确保node版本为6.5以上. // 安装脚手架 npm i dva-cli -g // 自动安装新工程 dva new newProjectName // 导入antd包 npm i an ...

将jsp页面转化为图片或pdf(一)

在项目中遇见了将jsp页面转化为pdf的问题,试过itext,但是itext需要标准的html代码,我的页面中的一些属性是itext所不识别的,所以努力了一段时间后就放弃了,后来发现htmlutil抓 ...

Ubunbu新建的用户使用SecureCrt无法Table补全、无法高亮

Check 两个地方: 1.  确保/etc/passwd中配置有/bin/bash (这个是用来控制补全). 2. 在~/.bashrc中配置, export TERM=linux (这个是用来控制 ...

C++面向对象编程解决三阶矩阵相加减

/*此处用面向对象编程*/ #include #include using namespace std; class Matrices ...

[置顶] 第二届微软CRM交流年会

第二届微软CRM交流会将在12月14日举行,亲们要是感兴趣可以查看下面的活动详情.Jeff也是第一次参加这类活动,作为本次活动的嘉宾我为大家带来一个挺有意思的分享主题

[Angular Tutorial] 4 - Directory and File Organization

在这一步中,我们将不会在我们的应用中添加任何新功能,相反,我们打算退回一步,重构我们的代码库,移动我们的代码和文件,以此来使我们的应用更具易扩展性和可维护性. 在先前的步骤中,我们已经见识到了如何将我 ...

ansible实践2-拷贝文件或目录

ansible testhost -m copy -a "src=/etc/ansible  dest=/tmp/ansibletest owner=root group=root mo ...

BeautifulSoup 获取无标签文本

比如:

aaa

bbb

ccc

ddd 怎么获取bbb和ddd呢? 结果:

Python基础【day02】：列表(三)

本节内容 0.列表常用功能汇总1.定义列表2.访问列表中的元素3.切片4.追加5.插入6.修改7.拷贝8.删除9.扩展10.统计11.翻转12.排序13.获取下标值14.清空列表 列表是Python最 ...

windows 8，关闭随意窗体都提示“已停止工作”的解决的方法

电脑用的好好的,突然出现了关闭随意窗体都提示"已停止工作",每次都是这样.想想也就是偶尔优化一下,近期也没有改动过什么系统设置呀,突然想到昨晚JLink_V8固件丢失 ...