Gorm.Clause()子句分析之ON DUPLICATE KEY UPDATE

Gorm.Clause()子句分析之ON DUPLICATE KEY UPDATE

原文:gorm.Clause()子句分析之ON DUPLICATE KEY UPDATE - 白春雨 - 博客园 (cnblogs.com)

一、背景介绍

  最近看到一段代码,使用到在了gorm的Clause()子句,大概如图所示。之前由于没用过Clause()子句,所以本文对Clause()子句先进行研究,然后分析sql语句。

image

二、Clause()子句

  GORM 内部使用 SQL builder 生成 SQL。对于每个操作,GORM 都会创建一个 *gorm.Statement 对象,所有的 GORM API 都是在为 statement 添加/修改 Clause,最后,GORM 会根据这些 Clause 生成 SQL。例如,当通过 First 进行查询时,它会在 Statement 中添加以下 Clause :

Text
1
2
3
4
5
6
clause.Select{Columns: "*"}
clause.From{Tables: clause.CurrentTable}
clause.Limit{Limit: 1}
clause.OrderByColumn{
Column: clause.Column{Table: clause.CurrentTable, Name: clause.PrimaryKey},
}

然后 GORM 在 Query callback 中构建最终的查询 SQL,像这样:

Text
1
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR")

生成 SQL:

Text
1
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

您可以自定义 Clause 并与 GORM 一起使用,这需要实现 Interface 接口,

2.1、子句构造器

  不同的数据库, Clause 可能会生成不同的 SQL,例如:

Text
1
2
3
4
5
db.Offset(10).Limit(5).Find(&users)
// SQL Server 会生成
// SELECT * FROM "users" OFFSET 10 ROW FETCH NEXT 5 ROWS ONLY
// MySQL 会生成
// SELECT * FROM `users` LIMIT 5 OFFSET 10

  子句选项之所以支持 Clause,是因为 GORM 允许数据库驱动程序通过注册 Clause Builder 来取代默认值,GORM 定义了很多 Clause,其中一些 Clause 提供了你可能会用到的选项,尽管很少会用到它们,但如果你发现 GORM API 与你的预期不符合。这可能可以很好地检查它们,例如:

Text
1
2
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
// INSERT IGNORE INTO users (name,age...) VALUES ("jinzhu",18...);

三、ON DUPLICATE KEY UPDATE

  针对本文开头的代码,加上Debug(),打印出sql语句如下:

Text
1
INSERT INTO `user_info` (`user_id`,`door_id`,`email`,`address`,`create_time`,`update_time`) VALUES (666,888,'test123@qq.com','北京市海淀区','2021-07-28 22:26:20.241','2021-07-28 22:26:20.241') ON DUPLICATE KEY UPDATE `email`=VALUES(`email`),`address`=VALUES(`address`),`update_time`=VALUES(`update_time`)

  使用这条语句的原因,是为了更好的执行插入和更新,因为我们在插入一条数据时,表中可能已经存在了这条数据,这时我们想实现更新的功能,或者表中没有这条数据,我们想实现插入的功能,而上面这条sql语句可以同时实现插入和更新的功能。

  那么这条sql语句是如何解释呢?我们很容易理解前面的部分,就是一个简单的插入语句,让我们看下后面的部分ON DUPLICATE KEY UPDATE `email`=VALUES(`email`),`address`=VALUES(`address`),`update_time`=VALUES(`update_time`)我们看到后面是一个更新的操作,并且指定了更新的字段,也就是说,如果判断出表中没有这条数据,执行的前半部分,插入指定字段的值;如果判断出表中有这条数据,则执行后面的更新操作,更新后半部分指定的字段的值。

  那么下一个问题出来了,我们是如何判断出这条数据是存在的,又需要更新哪些字段呢?

  规则如下:

  如果你插入的记录导致UNIQUE索引重复,那么就会认为该条记录存在,则执行update语句而不是insert语句,反之,则执行insert语句而不是更新语句。

  比如我创建表的时候设置的唯一索引为字段(a,b,c),那么当a,b,c三个字段完全重复时候,此时就要执行更新语句。当然满足一部分唯一索引是不会触发更新操作的,此时会执行插入操作。

  而至于要更新哪些字段,要看我们自己的需求了。

3.1、ON DUPLICATE KEY UPDATE 实践

  先声明一点:ON DUPLICATE KEY UPDATE 这个子句是MySQL特有的,语句的作用是,当insert已经存在的记录时,就执行update。

举例说明:

  user表中有一条数据如下:

image

 表中的主键为id,现要插入一条id为2的数据,正常写法为:

Text
1
insert into user(id,user_id,user_name,email,address,create_time,update_time) values(2,3764,'李四','lisi@qq.com','北京市东城区',now(),now());

执行后刷新表数据,我们来看表中内容:

image

此时表中数据增加了一条id为2的记录,当我们再次执行插入语句时,会发生什么呢?

image

Mysql告诉我们,我们的主键冲突了,看到这里我们是不是可以改变一下思路,当插入已存在主键的记录时,将插入操作变为修改:

Text
1
2
// 在原sql后面增加 ON DUPLICATE KEY UPDATE 
insert into user(id,user_id,user_name,email,address,create_time,update_time) values(2,3764,'李四','lisi@qq.com','北京市东城区',now(),now()) ON DUPLICATE KEY UPDATE user_name='王五',email='wangwu@qq.com',address='河北省保定市';

我们执行上面的sql,并刷新表:

image

可以看到原有的数据被修改了,而不是执行插入。原本id为2的记录,改为了’王五’,‘wangwu@qq.com‘,’河北省保定市’,很好的解决了重复插入问题。

3.2、VALUES() 动态修改

  那么问题来了,有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值,如果我想要分别给不同的记录插入不同的值怎么办呢?

Text
1
insert into user(id,user_id,user_name,email,address,create_time,update_time) values(2,3764,'孙六','sunliu@qq.com','上海市红桥区',now(),now()) ON DUPLICATE KEY UPDATE user_name=VALUES(user_name),email=VALUES(email),address=VALUES(address);

  可以将后面的修改条件改为使用VALUES()函数,动态的传入要修改的值,执行上述sql,并刷新表:

image

四、总结

  即便如此,在实际开发中,我们仍然不推荐这种写法,因为这种写法耦合了add和update两种操作,线上出现bug时,极难定位问题。推荐的做法是:单写一个add方法,只负责插入数据,插入重复数据时,根据业务场景做幂等性处理;单写一个update方法,只负责更新操作。两个函数单独打自己的log,便于定位问题。