般思路是将其实现分为两块分别是判断增加判断更新后来发现在mysql中有ON

DUPLICATE

子句而要插入的行与表中现有记录的惟一索引或主键中产生重复值那么就会发生旧行的更新如果插入的行数据与现有表中记录的唯一索引或者主键不重复则执行新纪录插入操作。

说通俗点就是数据库中存在某个记录时执行这个语句会更新而不存在这条记录时就会插入。

注意点

如果是插入操作受到影响行的值为1如果更新操作受到影响行的值为2如果更新的数据和已有的数据一样就相当于没变所有值保持不变受到影响的行的值为0。

该语句是基于唯一索引或主键使用比如一个字段a被加上了unique

INSERT

idinsertOrUpdateCameraInfoByOne

paramerTypecom.pojo.AreaInfoinsert

into

cameraId,zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4NameVALUES(#{cameraId},#{zone1Id},#{zone1Name},

#{zone3Id},

idinsertOrUpdateCameraInfoByBatch

parameterTypejava.util.Listinsert

into

camera_info(zone1Id,zone1Name,zone2Id,zone2Name,zone3Id,zone3Name,zone4Id,zone4Name,cameraId)VALUESforeach

collection

#{cameraInfo.zone2Id},#{cameraInfo.zone2Name},

#{cameraInfo.zone3Name},#{cameraInfo.zone4Id},

)/foreachON

需要将数据从A数据库的a数据表同步到B数据库的b数据表中ab表结构相同但不是主从关系。

。

。

just同步过去

但是当a表中的某些数据更新且增加了新数据之后再想让两个表同步就有些麻烦了。

如果把b表清空重新同步数据量过大的话耗费的时间太长不是一个好办法

想着能不能按照时间段来做更新这段时间内有新数据了就插入数据有数据更新了就更新数据。

先说下我的思路

2.往b表内放数据根据主键判断b表是否已经有此条记录没有此数据则插入有了记录则对比数据是否一样一样则不做更改不一样就做更新操作。

key或者primary

key最好保证一个不然不能保证语句执行正确(有任意一个unique

key重复就会走更新,当然如果更新的语句中在表中也有重复校验的字段那么也不会更新成功而导致报错,只有当该条语句没有任何一个unique

key重复才会插入新记录)尽量不对存在多个唯一键的table使用该语句避免可能导致数据错乱。

如果数据表id是自动递增的不建议使用该语句id不连续如果前面更新的比较多新增的下一条会相应跳跃的更大。

该语句是mysql独有的语法如果可能会设计到其他数据库语言跨库要谨慎使用。

主键不连续自增解决方法

源引自https://www.linuxidc.com/Linux/2018-01/150427.htm

最近项目上需要实现这么一个功能:统计每个人每个软件的使用时长,客户端发过来消息,如果该用户该软件已经存在增更新使用时间,如果没有则新添加一条记录,代码如下:

!--

app_table(userName,app,duration)

values(#{userName},#{item.app},#{item.duration})

duplicate

durationduration#{item.duration}

/foreach

update进行自动判断是更新还是新增,一段时间后发现该表的主键id(已设置为连续自增),不是连续的自增,总是跳跃的增加,这样就造成id自增过快,已经快超过最大值了,通过查找资料发现,on

duplicate

update有一个特性就是,每次是更新的情况下id也是会自增加1的,比如说现在id最大值的5,然后进行了一次更新操作,再进行一次插入操作时,id的值就变成了7而不是6.

为了解决这个问题,有两种方式,第一种是修改innodb_autoinc_lock_mode中的模式,第二种是将语句修拆分为更新和操作2个动作

第一种方式:innodb_autoinc_lock_mode中有3中模式,0,1和2,mysql5的默认配置是1,

1只有在bulk

insert的时候才会锁表,简单insert的时候只会使用一个light-weight

数据库默认是1的情况下,就会发生上面的那种现象,每次使用insert

into

的时候都会把简单自增id增加,不管是发生了insert还是update

由于该代码数据量大,同时需要更新和添加的数据量多,不能使用将0模式,只能将数据库代码拆分成为更新和插入2个步骤,第一步先根据用户名和软件名更新使用时长,代码如下:

update

然后根据返回值,如果返回值大于0,说明更新成功不再需要插入数据,如果返回值小于0则需要进行插入该条数据,代码如下:

insert

app_table(userName,appName,duration)

values(#{userName},#{appName},#{duration})

/insert

在执行时innodb引擎会先判断插入的行是否产生重复key错误如果存在在对该现有的行加上S共享锁锁如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作然后对该记录加上X排他锁最后进行update写入。

lock如

https://www.cnblogs.com/zjdxr-up/p/8319982.html

INSERT