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

2023-11-20 14:33:46

一、背景介绍

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

二、Clause()子句

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

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，像这样：

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

生成 SQL：

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

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

2.1、子句构造器

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

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 与你的预期不符合。这可能可以很好地检查它们，例如：

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

三、ON DUPLICATE KEY UPDATE

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

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`)

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

　　那么这条语句是如何解释呢，我们很容易理解前面的部分，就是一个简单的插入语句，让我们看下后面的部分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的数据，正常写法为：

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告诉我们，我们的主键冲突了，看到这里我们是不是可以改变一下思路，当插入已存在主键的记录时，将插入操作变为修改：

// 在原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，并刷新表：

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

3.2、VALUES修改

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

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，并刷新表：

四、总结

　　以上介绍的是addOrUpdate的语义，其实修改的方法有很多种，包括SET或用REPLACE，连事务都省的做，ON DUPLICATE KEY UPDATE能够让我们便捷的完成重复插入的开发需求，但它是Mysql的特有语法，使用时应多注意主键和插入值是否是我们想要插入或修改的数据。

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

码农公寓