INSERT ... ON DUPLICATE KEY UPDATE

向数据库插入记录时,有时会有这种需求,当符合某种条件的数据存在时,去修改它,不存在时,则新增,也就是insertOrUpdate操作。这种控制可以放在业务层,也可以放在数据库层,大多数数据库都支持这种需求,如Oracle的merge语句,再如本文所讲的MySQL中的INSERT ... ON DUPLICATE KEY UPDATE语句。

       该语句是基于唯一索引或主键使用,比如一个字段a被加上了unique index,并且表中已经存在了一条记录值为1,下面两个语句会有相同的效果:

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

UPDATE table SET c=c+1 WHERE a=1;

       ON DUPLICATE KEY UPDATE后面可以放多个字段,用英文逗号分割。使用ON DUPLICATE KEY UPDATE,最终如果插入了一个新行,则受影响的行数是1,如果修改了已存在的一行数据,则受影响的行数是2,如果值不变,则受影响行数是0。

       如果字段b也被加上了unique index,则该语句和下面的update语句是等效的:

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

       如果a=1 OR b=2匹配了多行,则只有一行会被修改。通常的,在ON DUPLICATE KEY UPDATE语句中,我们应该避免多个唯一索引的情况。

       可以在ON DUPLICATE KEY UPDATE后面使用VALUES(字段名)函数来表示即将插入的值,如果需要插入或更新多条数据,并且更新的字段需要根据其它字段来运算时,可以使用如下语句:

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

       这个语句等同于下面的两个语句:

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

       如果一个表中包含了一个自增的字段,使用INSERT ... ON DUPLICATE KEY UPDATE新增或修改t数据后,可以通过last_insert_id()方法返回最后自动生成的值,如果是多条,实际测试是返回第一条数据自增的值。

       使用ON DUPLICATE KEY UPDATE时,将忽略DELAYED选项。

       英文原文:http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Albert陈凯

2018-11-26 oracle查询表信息(索引,外键,列等)1、查询出所有的用户表2、查询出用户所有表的索引3、查询用户表的索引(非聚集索引):4、查询用户表的主键(聚集索引):5、查询表的索引6

oracle中查询表的信息,包括表名,字段名,字段类型,主键,外键唯一性约束信息,索引信息查询SQL如下,希望对大家有所帮助:

12420
来自专栏Java成神之路

解决hibernate向mysql插入中文乱码问题

MySQL会出现中文乱码的原因不外乎下列几点:    1.server本身设定问题,例如还停留在latin1    2.table的语系设定问题(包含chara...

9530
来自专栏技术博文

mysql UNIX时间戳与日期的相互转换

UNIX时间戳转换为日期用函数: FROM_UNIXTIME() select FROM_UNIXTIME(1156219870); 日期转换为UN...

40280
来自专栏乐沙弥的世界

SQL与mongoDB对比及映射

mongoDB是最接近与关系型数据库的开源NoSQL数据库,几乎绝大多数常用的命令或语句在SQL中都可以找到相应的对应或映射。本文主要与MySQL做了些参照,供...

17820
来自专栏nnngu

019 单例模式的5种写法

单例模式的定义:保证一个类仅有一个实例,并提供一个访问它的全局访问点! 1、懒汉 public class Singleton { private st...

29940
来自专栏后台日记

Mysql INSERT ON DUPLICATE KEY UPDATE

INSERT ... ON DUPLICATE KEY UPDATE是MySQL insert的一种扩展。当发现有重复的唯一索引(unique key)或者主键...

16430
来自专栏康怀帅的专栏

MySQL 初级命令

本文列举了一些初级的 MySQL 命令。 创建 数据库 CREATE DATABASE 数据库名; 数据表 CREATE TABLE IF NOT EXISTS...

29570
来自专栏不想当开发的产品不是好测试

mysql 时间格式化

将时间转为格式化的字符串 select date_format(now(),'%Y%m%d'); ? 将时间字符串转为时间戳 select unix_times...

23860
来自专栏程序你好

数据库MySQL中的JOIN详解

一个完整的SQL语句中会被拆分成多个子句,子句的执行过程中会产生虚拟表(vt),但是结果只返回最后一张虚拟表。从这个思路出发,我们试着理解一下JOIN查询的执行...

6610
来自专栏有趣的django

14.MySQL(二) 数据之表操作表内容操作Mysql 连接事务外键

数据之表操作 1.创建表 语法:CREATE TABLE table_name (column_name column_type); create table...

37290

扫码关注云+社区

领取腾讯云代金券