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语句。
二、Clause()子句
GORM 内部使用 SQL builder 生成 SQL。对于每个操作,GORM 都会创建一个 *gorm.Statement
对象,所有的 GORM API 都是在为 statement
添加/修改 Clause
,最后,GORM 会根据这些 Clause 生成 SQL。例如,当通过 First
进行查询时,它会在 Statement
中添加以下 Clause :
1 | clause.Select{Columns: "*"} |
然后 GORM 在 Query
callback 中构建最终的查询 SQL,像这样:
1 | Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR") |
生成 SQL:
1 | SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1 |
您可以自定义 Clause
并与 GORM 一起使用,这需要实现 Interface 接口,
2.1、子句构造器
不同的数据库, Clause 可能会生成不同的 SQL,例如:
1 | db.Offset(10).Limit(5).Find(&users) |
子句选项之所以支持 Clause,是因为 GORM 允许数据库驱动程序通过注册 Clause Builder 来取代默认值,GORM 定义了很多 Clause,其中一些 Clause 提供了你可能会用到的选项,尽管很少会用到它们,但如果你发现 GORM API 与你的预期不符合。这可能可以很好地检查它们,例如:
1 | db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user) |
三、ON DUPLICATE KEY UPDATE
针对本文开头的代码,加上Debug(),打印出sql语句如下:
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表中有一条数据如下:
表中的主键为id,现要插入一条id为2的数据,正常写法为:
1 | insert into user(id,user_id,user_name,email,address,create_time,update_time) values(2,3764,'李四','lisi@qq.com','北京市东城区',now(),now()); |
执行后刷新表数据,我们来看表中内容:
此时表中数据增加了一条id为2的记录,当我们再次执行插入语句时,会发生什么呢?
Mysql告诉我们,我们的主键冲突了,看到这里我们是不是可以改变一下思路,当插入已存在主键的记录时,将插入操作变为修改:
1 | // 在原sql后面增加 ON DUPLICATE KEY UPDATE |
我们执行上面的sql,并刷新表:
可以看到原有的数据被修改了,而不是执行插入。原本id为2的记录,改为了’王五’,‘wangwu@qq.com‘,’河北省保定市’,很好的解决了重复插入问题。
3.2、VALUES() 动态修改
那么问题来了,有人会说我ON DUPLICATE KEY UPDATE 后面跟的是固定的值,如果我想要分别给不同的记录插入不同的值怎么办呢?
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,并刷新表:
四、总结
即便如此,在实际开发中,我们仍然不推荐这种写法,因为这种写法耦合了add和update两种操作,线上出现bug时,极难定位问题。推荐的做法是:单写一个add方法,只负责插入数据,插入重复数据时,根据业务场景做幂等性处理;单写一个update方法,只负责更新操作。两个函数单独打自己的log,便于定位问题。