|
简述
根据业务背景（将备份的数据覆盖当前记录），做冲突时批量更新操作。
MySQL支持的批量更新操作。
insert into...on duplicate key update 的 自增 ID跳跃和处理方式。
插入失败自增 ID 增加问题。
GORM实现的冲突时批量更新操作。
目录
MySQL支持的批量更新 1、insert into...on duplicate key update
插入操作。主键（PRIMARY KEY）或者唯一索引（unique key）冲突时，执行update，除主键外字段都更新。支持批量。values是针对该语句添加批量数据的。mysql8.0以后values推荐其他写法。
注意：经过测试，唯一值（unique）也会触发更新。不必须是索引和主键。
insert into users (id, job, age) values (1, 'job11', 11),(2, 'job22', 22)
on duplicate key update job=values(job), age=values(age);
1.1 自增id跳跃现象
执行该SQL，利用 name 字段唯一的冲突更新记录时，下次插入记录，自增id会跳跃（该SQL会占用一个自增id的值）。
# id 自增主键， name 唯一。
# 原始记录
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 26 |
+----+-------+------+
# 更新id=13的记录
mysql> insert into for_update(name, age) values('diliu', 16)
on duplicate key update name=values(name), age=values(age);
# 更新后的记录
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 16 |
+----+-------+------+
# 插入一条新记录
mysql> insert into for_update(name, age) values('qi',27);
# 新插入的记录自增id=15，跳过14
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 16 |
| 15 | qi | 27 |
+----+-------+------+
1.2 解决自增id跳跃问题
暂时忽略方法：网上说修改innodb_autoinc_lock_mode 改变加锁的范围，有0，1，2模式，网上说设置0，加表锁，解决id跳跃。但是并发不好。
实际使用方法：执行insert into...on duplicate key update 语句时，指定自增id 的值，利用id冲突更新。
自增 id 的自增情况：
特殊情况：insert插入有冲突会报错MySQL 处理重复数据，但是自增id会增加。
经过测试：id 冲突时，自增id不会自增。
注意：暂时不知道哪些插入失败的情况id不会增加，不知道为什么不会增加
# id 自增主键，name 唯一值
# 原始记录
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 16 | ba | 28 |
| 17 | jiu | 29 |
| 18 | ershi | 210 |
+----+-------+------+
# 插入name冲突记录，失败，id增加
mysql> insert into for_update(name, age) values('ershi',210);
ERROR 1062 (23000): Duplicate entry 'ershi' for key 'for_update.name'
# 插入新记录，成功
mysql> insert into for_update(name, age) values('ershiyi',211);
Query OK, 1 row affected (0.00 sec)
# id=20，跳过19
mysql> select * from for_update;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 16 | ba | 28 |
| 17 | jiu | 29 |
| 18 | ershi | 210 |
| 20 | ershiyi | 211 |
+----+---------+------+
# 插入id冲突记录，失败，id不自增
mysql> insert into for_update(id, name, age) values(20, 'ershiyi',211);
ERROR 1062 (23000): Duplicate entry '20' for key 'for_update.PRIMARY'
# 插入一条记录，成功，id没有跳跃
mysql> insert into for_update(name, age) values('ershier',212);
Query OK, 1 row affected (0.00 sec)
mysql> select * from for_update;
+----+---------+------+
| id | name | age |
+----+---------+------+
| 16 | ba | 28 |
| 17 | jiu | 29 |
| 18 | ershi | 210 |
| 20 | ershiyi | 211 |
| 21 | ershier | 212 |
+----+---------+------+
利用自增id冲突，更新数据：根据上面的情况，在执行insert into...on duplicate key update 语句时，指定 id 的值。有冲突会更新，此时更新不会导致子增id增加。
# id 自增主键， name 唯一。
# 原始记录
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 16 |
| 15 | qi | 27 |
+----+-------+------+
# 更新id=15的记录，并且value的 id 字段指定为 15
mysql> insert into for_update(id, name, age) values(15, 'qi', 36) on duplicate
key update id =values(id), name=values(name), age=values(age);
# 记录更新age=36
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 16 |
| 15 | qi | 36 |
+----+-------+------+
# 插入一条新记录
mysql> insert into for_update(name, age) values('ba',28);
# 自增 id=16 没有发生跳跃
mysql> select * from for_update;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 12 | disi | 25 |
| 13 | diliu | 16 |
| 15 | qi | 36 |
| 16 | ba | 28 |
+----+-------+------+
2、replace into
主键（PRIMARY KEY）或者唯一索引（unique key）冲突时，先删除此记录，再重新插入。字段不全设置为默认值。支持批量。
注意：同上猜测唯一值（unique）也会冲突。
replace into users(id, job, age) VALUES (1, 'job111', 111),(2, 'job222', 222);
3、update...set...case...when...where
较麻烦。支持批量。当id=1时，job=‘job11’。当id=2时，job=‘job12'。
update users
set job = case id
when 1 then 'job11'
when 2 then 'job12'
end,
age = case id
when 1 then 11
when 2 then 12
end
where id IN (1, 2);
GORM支持的批量更新 业务背景
之前备份的一批数据，现在要重新写入数据库，并且覆盖当前记录。
处理方法
将之前的数据软删除
tx.Where("my_id = ?", myId).Delete(&Mod{})
冲突更新
GORM支持MySQL的insert into...on duplicate key update 语句，插入冲突时更新记录，支持批量。
// 在冲突时，更新除主键以外的所有列到新值。
db.Clauses(clause.OnConflict{UpdateAll: true}).Create(&users)
除次以外，GORM还提供冲突部分更新、自定义冲突字段的功能。
// 在`id`冲突时，将列更新为新值
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.AssignmentColumns([]string{"name", "age"}),
}).Create(&users)
// 在`id`冲突时，将列更新为默认值
db.Clauses(clause.OnConflict{
Columns: []clause.Column{{Name: "id"}},
DoUpdates: clause.Assignments(map[string]interface{}{"role": "user"}),
}).Create(&users)
注意
如果冲突不来自自增ID，冲突更新时，ID会增加，产生ID跳跃现象。
（编辑：晋中站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
