1、gorm的常用用法。自定义类型。clause
2、gorm+gen 提高crud的速度
3、trace链路追踪。
4、gorm集群和分库分表。
5、日志。
6、gorm.DB链式操作需要注意的地方
https://gorm.io/zh_CN/docs/index.html
gorm提供的文档非常齐全了，总结一下在工作中的一些实践，如果对您有帮我，您的star就是对我的鼓励https://github.com/luxun9527/go-lib 如有任何问题也欢迎留言。
**安装mysql**
```powershell
docker run -p 33606:3306 --name mysql8 --restart always \
-e MYSQL_ROOT_PASSWORD=root \
-v /root/docker/mysql/conf/my.cnf:/etc/mysql/my.cnf \
-v /root/docker/mysql/data:/var/lib/mysql \
-d mysql:8.0
```
## 1、gorm的常用用法,curd,自定义类型，clause子句。
### 新增
```go
func TestCreate(t *testing.T) {
user := &User{
Username: "",
Age: 0,
Fav: "",
CreatedAt: 0,
UpdatedAt: 0,
}
//INSERT INTO `user` (`username`,`age`,`fav`,`created_at`,`updated_at`) VALUES ('',0,'',1692947238,1692947238)
if err := db.Create(user).Error; err != nil {
log.Panicf("create no select err %v", err)
}
log.Printf("user1 = %+v", user)
//INSERT INTO `user` (`fav`,`created_at`,`updated_at`) VALUES ('',1692947405,1692947405)
if err := db.Select("fav").Create(user).Error; err != nil {
log.Panicf("create select field err %v", err)
}
log.Printf("user2 = %+v", user)
//INSERT INTO `user` (`username`,`age`,`created_at`,`updated_at`,`id`) VALUES ('',0,1692947813,1692947813,14)
if err := db.Omit("fav").Create(user).Error; err != nil {
log.Panicf("create Omit field err %v", err)
}
log.Printf("user2 = %+v", user)
//https://gorm.io/zh_CN/docs/models.html https://gorm.io/zh_CN/docs/create.html#%E9%BB%98%E8%AE%A4%E5%80%BC
//官方的文档很全主要是验证一些模糊的地方
//1、不使用select指定，零值字段也会被插入。使用select只会插入指定的字段。可以使用tag指定默认值 `gorm:"default:18"`
//2、created_at updated_at会被填充当前时间插入。 https://gorm.io/zh_CN/docs/models.html#%E5%88%9B%E5%BB%BA-x2F-%E6%9B%B4%E6%96%B0%E6%97%B6%E9%97%B4%E8%BF%BD%E8%B8%AA%EF%BC%88%E7%BA%B3%E7%A7%92%E3%80%81%E6%AF%AB%E7%A7%92%E3%80%81%E7%A7%92%E3%80%81Time%EF%BC%89
//3、插入后会将主键赋值回来，使用select的方式插入不会赋值回来。
}
2024/04/21 23:24:21 E:/demoproject/go-lib/sdk/gorm/curd_test.go:74
[3.177ms] [rows:1] INSERT INTO `user` (`username`,`age`,`fav`,`created_at`,`updated_at`,`deleted_at`) VALUES ('',0,'',1713713061,1713713061,'0')
2024/04/21 23:24:21 user1 = &{ID:7 Username: Age:0 Fav: CreatedAt:1713713061 UpdatedAt:1713713061 DeletedAt:0}
2024/04/21 23:24:21 E:/demoproject/go-lib/sdk/gorm/curd_test.go:79
[3.227ms] [rows:1] INSERT INTO `user` (`fav`,`created_at`,`updated_at`) VALUES ('',1713713061,1713713061)
2024/04/21 23:24:21 user2 = &{ID:7 Username: Age:0 Fav: CreatedAt:1713713061 UpdatedAt:1713713061 DeletedAt:0}
2024/04/21 23:24:21 E:/demoproject/go-lib/sdk/gorm/curd_test.go:84 Error 1062 (23000): Duplicate entry '7' for key 'user.PRIMARY'
[0.534ms] [rows:0] INSERT INTO `user` (`username`,`age`,`created_at`,`updated_at`,`deleted_at`,`id`) VALUES ('',0,1713713061,1713713061,'0',7)
2024/04/21 23:24:21 create Omit field err Error 1062 (23000): Duplicate entry '7' for key 'user.PRIMARY'
--- FAIL: TestCreate (0.01s)
panic: create Omit field err Error 1062 (23000): Duplicate entry '7' for key 'user.PRIMARY' [recovered]
panic: create Omit field err Error 1062 (23000): Duplicate entry '7' for key 'user.PRIMARY'
```
### 修改
```go
func TestUpdate(t *testing.T) {
u := &User{ID: 1}
//更新单个列 只有非空的字段会被修改
//UPDATE `user` SET `username`='hello',`updated_at`=1692948530 WHERE `id` = 1
db.Model(&u).Update("username", "hello")
//更新多个列,只有非空的字段会被修改
// UPDATE `user` SET `username`='hello12',`updated_at`=1692948530 WHERE `id` = 1
db.Model(&u).Updates(&User{Username: "hello12"})
//select 更新指定了列,不会忽略零值
// UPDATE `user` SET `username`='',`updated_at`=1692948530 WHERE `id` = 1
db.Model(&u).Select("username").Updates(&User{
Username: "",
Fav: "",
})
//忽略指定的列，会忽略空值。
//UPDATE `user` SET `updated_at`=1695796004 WHERE `id` = 1
db.Model(&u).Omit("fav").Updates(&User{
Username: "",
Fav: "1",
})
//UPDATE `user` SET `id`=0,`username`='',`age`=0,`created_at`=0,`updated_at`=1695796443,`deleted_at`=0 WHERE `id` = 1
//select 所有再忽略指定的列，不会忽略零值
db.Select("*").Omit("fav").Updates(&User{
ID: 1,
Username: "",
Fav: "1",
})
//指定条件更新，会忽略空值
// UPDATE `user` SET `id`=1,`username`='1',`updated_at`=1692948928 WHERE username = 'admin' AND `id` = 1
db.Where("username = ?", "admin").Updates(User{Username: "1", ID: 1})
//默认没有指定条件不会全局更新。db.Model(&User{}).Update("name", "jinzhu").Error gorm.ErrMissingWhereClause
//如果要更新零字段，要使用map或者select指定字段。 select 所有不会忽略零值
// UPDATE `user` SET `id`=1,`username`='',`age`=0,`fav`='',`created_at`=0,`updated_at`=1692948946 WHERE `id` = 1
db.Select("*").Updates(User{
ID: 1,
Username: "",
Age: 0,
Fav: "",
CreatedAt: 0,
UpdatedAt: 0,
})
}
```
### 删除
```go
func TestDelete(t *testing.T) {
//UPDATE `user` SET `deleted_at`=1713714861 WHERE id = 1 AND `user`.`deleted_at` = 0
db.Where("id = ?", 3).Delete(&User{})
//DELETE FROM `user` WHERE id = 3
db.Unscoped().Where("id = ?", 3).Delete(&User{})
}
```
### 查询
```go
func TestSelect(t *testing.T) {
//普通的查询，官方文档上写的比较全,主要是显示预加载。
//========================================一对一 一个用户有一个profile======================
var users1 []*User
if err := db.Model(&User{}).Where("id = ?", 6).Preload("Profile").Find(&users1).Error; err != nil {
log.Println(err)
}
//SELECT * FROM `card` WHERE `card`.`user_id` = 6
//SELECT * FROM `user` WHERE id = 6
//SELECT * FROM `profile` WHERE `profile`.`user_id` = 6
//===================================================一对多，一个用户有多个cards=================================
var users2 []*User
if err := db.Model(&User{}).Where("id = ?", 6).Preload("Cards").Preload("Profile").Find(&users2).Error; err != nil {
log.Println(err)
}
for _, v := range users2 {
log.Printf("%+v", v)
}
/*
[0.529ms] [rows:0] SELECT * FROM `card` WHERE `card`.`user_id` = 6
2024/04/23 22:57:55 E:/demoproject/go-lib/sdk/gorm/curd_test.go:215
[1.098ms] [rows:0] SELECT * FROM `profile` WHERE `profile`.`user_id` = 6
2024/04/23 22:57:55 E:/demoproject/go-lib/sdk/gorm/curd_test.go:215
[3.268ms] [rows:1] SELECT * FROM `user` WHERE id = 6 AND `user`.`deleted_at` = 0
2024/04/23 22:57:55 &{ID:6 Username: Age:0 Fav: CompanyID:1 CreatedAt:1713713017 UpdatedAt:1713713017 Cards:[] Profile:{ID:0 UserID:0 Nickname: Desc: CreatedAt:0 UpdatedAt:0 DeletedAt:0} Company:{ID:0 Name:} DeletedAt:0}
*/
//===============================belong to 一个用户属于一家公司=====================
var (
user User
company Company
)
//查用户所属的公司。
//SELECT * FROM `user` WHERE id = 1 AND `user`.`deleted_at` = 0 LIMIT 1
db.Where("id = ?", 6).Take(&user)
//SELECT * FROM `company` WHERE `company`.`id` = 1
if err := db.Debug().Model(&user).Association("Company").Find(&company); err != nil {
log.Println("belong to error", err)
}
user.Company = company
log.Printf("user =%+v", user)
//user ={ID:6 Username: Age:0 Fav: CompanyID:0 CreatedAt:1713713017 UpdatedAt:1713713017 Company:{ID:1 Name:test} DeletedAt:0}
//=====================================join 预加载==========================================================
printLine("join 预加载")
var u User
if err := db.Joins("Company").Take(&u, 6).Error; err != nil {
log.Panicf("join error %v", err)
}
//SELECT `user`.`id`,`user`.`username`,`user`.`age`,`user`.`fav`,`user`.`company_id`,`user`.`created_at`,`user`.`updated_at`,`user`.`deleted_at`,
//`Company`.`id` AS `Company__id`,`Company`.`name` AS `Company__name` FROM `user`
//LEFT JOIN `company` `Company` ON `user`.`company_id` = `Company`.`id` WHERE `user`.`id` = 4 AND `user`.`deleted_at` = 0 LIMIT 1
log.Printf("join u =%+v", u)
}
```
### 自定义类型
https://github.com/go-gorm/datatypes
https://gorm.io/zh_CN/docs/data_types.html#
自定义的数据类型必须实现 [Scanner](https://pkg.go.dev/database/sql#Scanner) 和 [Valuer](https://pkg.go.dev/database/sql/driver#Valuer) 接口，以便让 GORM 知道如何将该类型接收、保存到数据库https://github.com/go-gorm/datatypes/blob/master/date.go 示例
其他高级的用法参考https://gorm.io/zh_CN/docs/data_types.html#
```go
package main
import (
"database/sql"
"database/sql/driver"
"testing"
"time"
)
type CustomDateModel struct {
ID int32 `gorm:"column:id;not null;" json:"user_id"`
CustomDate Date `gorm:"column:custom_date;not null;" json:"custom_date"`
}
// TableName Card's table name
func (*CustomDateModel) TableName() string {
return "custom_date"
}
func TestCustomType(t *testing.T) {
db.Create(&CustomDateModel{CustomDate: Date(time.Now())})
//INSERT INTO `custom_date` (`custom_date`) VALUES ('2024-05-05 00:00:00')
}
type Date time.Time
//将数据库中的数据转换为Date类型
func (date *Date) Scan(value interface{}) (err error) {
nullTime := &sql.NullTime{}
err = nullTime.Scan(value)
*date = Date(nullTime.Time)
return
}
//将Date类型转换为数据库中的数据
func (date Date) Value() (driver.Value, error) {
y, m, d := time.Time(date).Date()
return time.Date(y, m, d, 0, 0, 0, 0, time.Time(date).Location()), nil
}
// GormDataType gorm common data type
func (date Date) GormDataType() string {
return "date"
}
func (date Date) GobEncode() ([]byte, error) {
return time.Time(date).GobEncode()
}
func (date *Date) GobDecode(b []byte) error {
return (*time.Time)(date).GobDecode(b)
}
func (date Date) MarshalJSON() ([]byte, error) {
return time.Time(date).MarshalJSON()
}
func (date *Date) UnmarshalJSON(b []byte) error {
return (*time.Time)(date).UnmarshalJSON(b)
}
```
### clause子句
https://blog.csdn.net/dorlolo/article/details/127238144
[https://gorm.io/zh_CN/docs/create.html#Upsert-%E5%8F%8A%E5%86%B2%E7%AA%81](https://gorm.io/zh_CN/docs/create.html#Upsert-及冲突)
gorm与子句生成器有关的类，按父级到子集排列为 [DB](https://github.com/go-gorm/gorm/blob/master/gorm.go#L89) **-->** [Statement](https://github.com/go-gorm/gorm/blob/master/statement.go) **-->** [Clause](https://github.com/go-gorm/gorm/blob/master/clause/clause.go) **-->** [Expression](https://github.com/go-gorm/gorm/blob/master/clause/expression.go) (分别对应 数据库连接对象–> 语句 --> 子句 --> 表达式),它们都是以属性形式保存在父类中。只要知道这个结构，看源码就会轻松很多。
```go
user := &User{}
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)
//INSERT INTO `user` (`username`,`age`,`fav`,`company_id`,`created_at`,`updated_at`,`deleted_at`) VALUES ('',0,'',0,1714919066,1714919066,0) ON DUPLICATE KEY UPDATE `id`=`id`
```
**2. 为单个字段实现开发构造器**
2.1 实现方法
只要自定义的模型字段中包含以下的其中一种方法即可，
```go
CreateClauses(*Field) []clause.Interface
QueryClauses(*Field) []clause.Interface
UpdateClauses(*Field) []clause.Interface
DeleteClauses(*Field) []clause.Interface
```
这个返回值类型可以实现StatementModifier接口 或者 clause.Interface接口都行。使用实例。
```go
package main
import (
"go-lib/sdk/gorm/gen/dao/model"
"gorm.io/gorm"
"gorm.io/gorm/clause"
"gorm.io/gorm/schema"
"testing"
)
type ClauseUser struct {
model.User
C CustomClause
}
func TestClauses(t *testing.T) {
user := &User{}
db.Clauses(clause.OnConflict{DoNothing: true}).Create(&user)
//INSERT INTO `user` (`username`,`age`,`fav`,`company_id`,`created_at`,`updated_at`,`deleted_at`) VALUES ('',0,'',0,1714919066,1714919066,0) ON DUPLICATE KEY UPDATE `id`=`id`
db.Clauses(clause.Eq{
Column: "id",
Value: 1,
}).Find(&user)
//SELECT * FROM `user` WHERE `id` = 1 AND `user`.`deleted_at` = 0 AND `user`.`id` = 19
db.Find(&ClauseUser{})
//SELECT * FROM `user` WHERE `user`.`c` IS NULL
}
type CustomClause int32
func (CustomClause) QueryClauses(f *schema.Field) []clause.Interface {
return []clause.Interface{CustomClauseQuery{Field: f}}
}
type CustomClauseQuery struct {
Field *schema.Field
}
func (sd CustomClauseQuery) Name() string {
return ""
}
func (sd CustomClauseQuery) Build(builder clause.Builder) {
}
func (sd CustomClauseQuery) MergeClause(c *clause.Clause) {
}
func (sd CustomClauseQuery) ModifyStatement(stmt *gorm.Statement) {
stmt.AddClause(clause.Where{Exprs: []clause.Expression{
clause.Eq{Column: clause.Column{Table: clause.CurrentTable, Name: sd.Field.DBName}, Value: nil},
}})
}
```
详情用法可以参考https://github.com/go-gorm/soft_delete 软删除的做法。
## 2、gorm + gen
https://gorm.io/zh_CN/gen/dao.html
https://gorm.io/zh_CN/gen/database_to_structs.html
相对于gorm gen提供对表的curd进一步的封装，能提高我们的开发效率，具体有如下优势。
**1、直接使用表结构生成model，不用自己手写model**
```
go install gorm.io/gen/tools/gentool@latest
gentool --dsn="root:root@tcp(192.168.2.200:33606)/test?charset=utf8mb4&parseTime=True&loc=Local" --db=mysql -outPath=gen/dao/query -fieldSignable=true
```
**如果有定制需求可以参考**。https://gorm.io/zh_CN/gen/database_to_structs.html
**有一些坑**
1、https://github.com/go-gorm/gen/issues/755自定义方法CommonMethod 需要和生成代码不在同一个包
[https://gorm.io/zh_CN/gen/database_to_structs.html#%E6%A8%A1%E6%9D%BF%E6%96%B9%E6%B3%95](https://gorm.io/zh_CN/gen/database_to_structs.html#模板方法)
```go
// Code generated by gorm.io/gen. DO NOT EDIT.
// Code generated by gorm.io/gen. DO NOT EDIT.
// Code generated by gorm.io/gen. DO NOT EDIT.
package model
const TableNameUser = "user"
// User mapped from table <user>
type User struct {
ID int32 `gorm:"column:id;primaryKey;autoIncrement:true" json:"id"`
Username string `gorm:"column:username;not null;comment:用户名" json:"username"`
Age uint32 `gorm:"column:age;not null;comment:年龄" json:"age"`
Fav string `gorm:"column:fav;not null;comment:爱好" json:"fav"`
CompanyID int32 `gorm:"column:company_id;not null;comment:公司Id" json:"company_id"`
CreatedAt uint64 `gorm:"column:created_at;not null;comment:创建时间" json:"created_at"`
UpdatedAt uint64 `gorm:"column:updated_at;not null;comment:修改时间" json:"updated_at"`
DeletedAt uint64 `gorm:"column:deleted_at;not null" json:"deleted_at"`
}
// TableName User's table name
func (*User) TableName() string {
return TableNameUser
}
```
**2、使用变量名而不是直接表字段名，避免写错字段。**
```go
gen ====> users, err := u.WithContext(ctx).Where(u.Name.Neq("modi")).Find()
gorm====> db.Where("name=?","modi").Find()
// SELECT * FROM users WHERE name <> 'modi';
```
**3、对查询的进一步封装,在查询上更加方便。**
```go
dao := query.Use(db)
//card := dao.Card
user := dao.User
company := dao.Company
ctx := context.Background()
_, err := user.WithContext(ctx).Find()
if err != nil {
log.Panicf("err:%v", err)
}
var u []*User
if db.Find(&u).Error != nil {
log.Panicf("err:%v", err)
}
//gen join
dao.User.WithContext(ctx).Join(company, user.CompanyID.EqCol(company.ID)).Find()
//gorm join
db.Joins("LEFT JOIN Company c ON c.id = u.company_id").Find(&u)
// gen分页
dao.User.WithContext(ctx).FindByPage(0, 10)
//gorm 分页
db.Offset(0).Limit(10).Find(&u)
// gen子查询
subQuery := company.WithContext(ctx).
Select(company.ID)
//
_, err = user.WithContext(ctx).
Where(user.Columns(user.CompanyID).In(subQuery)).
Find()
//SELECT * FROM `user` WHERE `user`.`company_id` IN (SELECT `company`.`id` FROM `company`)
```
**需要注意的的是：**
https://github.com/go-gorm/gen/issues/900 gen的Select方法和Where都是不能自定义的，当进行一些复杂查询的时候，需要自定义的时候如使用json函数`db.Select("JSON_OBJECT(key1,val1,key2,val2...)")` gen是无法做到的。但是我们可以使用ast来生成对应的代码。具体可以参考。https://github.com/luxun9527/go-lib/blob/master/utils/ast_apply/add_rawselect_rawmethod.go
## 3、trace链路追踪
将gorm加入到链路追踪中。使用WithContext传递。
```go
package main
import (
"context"
"fmt"
"github.com/uptrace/opentelemetry-go-extra/otelgorm"
"go.opentelemetry.io/otel"
"go.opentelemetry.io/otel/attribute"
"go.opentelemetry.io/otel/exporters/jaeger"
"go.opentelemetry.io/otel/sdk/resource"
tracesdk "go.opentelemetry.io/otel/sdk/trace"
semconv "go.opentelemetry.io/otel/semconv/v1.21.0"
"log"
"testing"
)
const (
service = "trace-demo1" // 服务名
environment = "production" // 环境
id = 1 // id
)
func tracerProvider(url string) (*tracesdk.TracerProvider, error) {
// Create the Jaeger exporter
// 创建 Jaeger exporter
exp, err := jaeger.New(jaeger.WithCollectorEndpoint(jaeger.WithEndpoint(url)))
if err != nil {
return nil, err
}
tp := tracesdk.NewTracerProvider(
// Always be sure to batch in production.
tracesdk.WithBatcher(exp),
// Record information about this application in a Resource.
tracesdk.WithResource(resource.NewWithAttributes(
semconv.SchemaURL,
semconv.ServiceNameKey.String(service),
attribute.String("environment", environment),
attribute.Int64("ID", id),
)),
)
return tp, nil
}
func TestGormTrace(t *testing.T) {
ctx := context.Background()
tp, err := tracerProvider("http://192.168.11.185:14268/api/traces")
if err != nil {
log.Fatal(err)
}
if err := db.Use(otelgorm.NewPlugin(
otelgorm.WithDBName("test"),
otelgorm.WithTracerProvider(tp),
)); err != nil {
log.Fatal(err)
}
otel.SetTracerProvider(tp)
tracer := otel.Tracer("gormtracer")
ctx, span := tracer.Start(ctx, "gormtest")
defer span.End()
user := &User{
Username: "test1",
Age: 1,
Fav: "1",
}
//INSERT INTO `user` (`username`,`age`,`fav`,`created_at`,`updated_at`) VALUES ('',0,'',1692947238,1692947238)
if err := db.WithContext(ctx).Create(user).Error; err != nil {
fmt.Println("create err", err)
}
//otelplay.PrintTraceID(ctx)
tp.Shutdown(ctx)
}
```
## 4、主从集群，分库分表
refer https://www.freesion.com/article/92701394177/
https://www.cnblogs.com/cao-lei/p/13603043.html
主从延迟 https://jishuin.proginn.com/p/763bfbd589f7
https://blog.csdn.net/JesseYoung/article/details/40585809
### mysql使用docker安装mysql主从集群
进入Master库mysql客户端 `mysql -uroot -h127.0.0.1 -P33307 -proot`。
如果本地没有mysql客户端则进入容器中
`docker exec -it mysql-master /bin/bash `在容器中的端口为 3306
输入`show master status`查看Master状态：
记住File和Position，后面需要用到。此时一定不要操作Master库，否则将会引起Master状态的变化，File和Position字段也将会进行变化。
#### 4.2 slave配置
```powershell
docker run -p 33307:3306 --name mysql8s --restart always \
-e MYSQL_ROOT_PASSWORD=root \
-v /root/docker/mysql/slave/conf/my.cnf:/etc/mysql/my.cnf \
-v /root/docker/mysql/slave/data:/var/lib/mysql \
-d mysql:8.0
```
进入mysql客户端执行命令
```
change master to master_host='192.168.2.200', master_user='root', master_password='root', master_port=33606, master_log_file='mysql-bin.000009', master_log_pos=814, master_connect_retry=30;
```
master_host ：Master库的地址局域网的ip
master_port：Master的端口号，指的是容器的端口号
master_user：用于数据同步的用户
master_password：用于同步的用户的密码
master_log_file：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
master_log_pos：从哪个 Position 开始读，即上文中提到的 Position 字段的值
master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒
2、 启动slave
```
start slave;
```
3、查看slave
```
show slave status \G;
```
### 4.3 slave常见错误
1、日志设置错误
```
master_log_file='mysql-bin1.000004', master_log_pos=157
```
检查日志是否和master的一致
2、master ip端口配置错误配置错误
### 其他命令
`stop slave; `停止slave
`reset master;` 重置master
### 测试是否成功
```plsql
#创建数据库
create DATABASE test1;
use test1;
#创建数据表
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
);
#插入数据
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date)
VALUES("学习 PHP", "菜鸟教程", NOW());
docker exec -it mysql8s mysql -uroot -proot
use test1;
select * from runoob_tbl;
```
### `gorm mysql主从集群`
https://gorm.io/zh_CN/docs/dbresolver.html
```go
package main
import (
"github.com/spf13/cast"
"github.com/zeromicro/go-zero/core/stringx"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"gorm.io/plugin/dbresolver"
"log"
"os"
"testing"
"time"
)
const TableNameRunoobTbl = "runoob_tbl"
// RunoobTbl mapped from table <runoob_tbl>
type RunoobTbl struct {
RunoobID int32 `gorm:"column:runoob_id;primaryKey;autoIncrement:true" json:"runoob_id"`
RunoobTitle string `gorm:"column:runoob_title;not null" json:"runoob_title"`
RunoobAuthor string `gorm:"column:runoob_author;not null" json:"runoob_author"`
SubmissionDate time.Time `gorm:"column:submission_date" json:"submission_date"`
}
// TableName RunoobTbl's table name
func (*RunoobTbl) TableName() string {
return TableNameRunoobTbl
}
const (
masterDsn = "root:root@tcp(192.168.2.200:33606)/test1?charset=utf8mb4&parseTime=True&loc=Local"
slaveDsn = "root:root@tcp(192.168.2.200:33307)/test1?charset=utf8mb4&parseTime=True&loc=Local"
)
// gentool --dsn="root:root@tcp(192.168.2.99:33307)/test1?charset=utf8mb4&parseTime=True&loc=Local" --onlyModel=true --db=mysql --tables=runoob_tbl -outPath=./ -fieldMap="decimal:string;tinyint:int32;"
func TestDbresolve(t *testing.T) {
newLogger := logger.New(
log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer
logger.Config{
SlowThreshold: time.Second, // Slow SQL threshold
LogLevel: logger.Info, // Log level
IgnoreRecordNotFoundError: true, // Ignore ErrRecordNotFound error for logger
Colorful: true, // Disable color
},
)
masterDB, err := gorm.Open(mysql.New(mysql.Config{
DSN: masterDsn,
}), &gorm.Config{
Logger: newLogger,
})
if err != nil {
log.Fatal(err)
}
if err := masterDB.Use(
dbresolver.Register(dbresolver.Config{
Sources: []gorm.Dialector{mysql.New(mysql.Config{
DSN: masterDsn,
})},
Replicas: []gorm.Dialector{mysql.New(mysql.Config{
DSN: slaveDsn,
})},
Policy: dbresolver.RandomPolicy{},
TraceResolverMode: true,
}).
SetMaxIdleConns(10).
SetConnMaxLifetime(time.Hour).
SetMaxOpenConns(200),
); err != nil {
log.Fatal(err)
}
d := &RunoobTbl{
RunoobTitle: cast.ToString(time.Now().Unix()),
RunoobAuthor: stringx.Randn(10),
SubmissionDate: time.Now(),
}
masterDB.Create(d)
if masterDB.Where("runoob_id=?", 2).Find(d).Error != nil {
log.Println(err)
}
log.Println(d)
/*
2024/05/05 21:48:01 E:/demoproject/go-lib/sdk/gorm/dbresolver_test.go:78
[9.653ms] [rows:1] [source] INSERT INTO `runoob_tbl` (`runoob_title`,`runoob_author`,`submission_date`) VALUES ('1714916881','XVG0Iay48N','2024-05-05 21:48:01.959')
2024/05/05 21:48:01 E:/demoproject/go-lib/sdk/gorm/dbresolver_test.go:80
[1.594ms] [rows:0] [replica] SELECT * FROM `runoob_tbl` WHERE runoob_id=2 AND `runoob_tbl`.`runoob_id` = 2
*/
}
```
### gorm 水平分表
https://gorm.io/zh_CN/docs/sharding.html
这个分表暂时不支持mysql如果mysql进行水平分表的话，要在业务代码中实现。
## 5、日志
https://gorm.io/zh_CN/docs/logger.html 自定义日志需要实现这几个接口。
```go
type Interface interface {
LogMode(LogLevel) Interface
Info(context.Context, string, ...interface{})
Warn(context.Context, string, ...interface{})
Error(context.Context, string, ...interface{})
Trace(ctx context.Context, begin time.Time, fc func() (sql string, rowsAffected int64), err error)
}
```
在实际的开发中常常使的比较多的是 `**moul.io/zapgorm2**` 这个库
## 6、gorm.DB链式操作需要注意的地方
**1、gorm 并发不安全。**
https://juejin.cn/post/7134002645651439630
首先，我们需要先去理解几乎每个方法中都会调用的函数：tx = db.getInstance()。
```go
func (db *DB) getInstance() *DB {
if db.clone > 0 {
tx := &DB{Config: db.Config, Error: db.Error}
if db.clone == 1 {
// clone with new statement
tx.Statement = &Statement{
DB: tx,
ConnPool: db.Statement.ConnPool,
Context: db.Statement.Context,
Clauses: map[string]clause.Clause{},
Vars: make([]interface{}, 0, 8),
}
} else {
// with clone statement
tx.Statement = db.Statement.clone()
tx.Statement.DB = tx
}
return tx
}
return db
}
```
将上述改写并简化一下，大概是这么个逻辑：
```go
func (db *DB) getInstance() *DB {
switch db.clone:
case 0:
return db
case 1:
return newStatement() // 一个全新的，空白的Statement
case 2:
return db.cloneStatement() // 将之前的Statement复制一份
}
```
当clone=1时，这个*gorm.DB 实例总是并发安全的，因为它总是会返回一个全新的*gorm.DB 实例，不会对老*gorm.DB 实例有什么读写。
当clone=2时，这个*gorm.DB 实例也总是并发安全的，因为任何的 Chain Method 和 Finisher Method 都只会去读和复制当前*gorm.DB 实例的值，而不会修改，因此只会对这个*gorm.DB 实例并发读，那么当然是并发安全的。
当clone=0时，这个*gorm.DB 实例就**不并发安全**。
那clone字段分别会在什么情况下等于0、1、2呢？
- **在使用gorm.Open()之后，新建出来的\*gorm.DB 实例clone字段总是1。**
- 在调用(*gorm.Gorm).Session()时，如果Session{}.NewDB为false，则为返回的*gorm.DB 实例clone字段是2，如果为true，则为1。
- 在调用(*gorm.Gorm).Session()时，如果Session{}.Initialized为true，则返回的*gorm.DB 实例clone字段是0。这条规则优先级高于Session.NewDB。
- **在调用了任意Chain Method、Finisher Method之后，返回的Gorm对象clone字段是0。**
在日常的开发中，我们喜欢将db定义为一个全局变量，**要注意的是绝对不要将一个db.clone=0的db赋值回全局变量下面是一个错误用法。**
```go
var users1 []*User
//初始的db.clone为1 执行where db.clone为0
db = db.Where("id = ?", 6)
db.Where("name = ?", "lisi").Find(&users1)
```
**下面也是链式操作不注意db.clone造成的问题。**
https://gorm.io/zh_CN/docs/method_chaining.html#Reusability-and-Safety
Reusability and Safety
A critical aspect of GORM is understanding when a *gorm.DB instance is safe to reuse. Following a Chain Method or Finisher Method, GORM returns an initialized *gorm.DB instance. This instance is not safe for reuse as it may carry over conditions from previous operations, potentially leading to contaminated SQL queries. For example:
### Example of Unsafe Reuse
```
queryDB := DB.Where("name = ?", "jinzhu") // First query queryDB.Where("age > ?", 10).First(&user) // SQL: SELECT * FROM users WHERE name = "jinzhu" AND age > 10 // Second query with unintended compounded condition queryDB.Where("age > ?", 20).First(&user2) // SQL: SELECT * FROM users WHERE name = "jinzhu" AND age > 10 AND age > 20
```
### Example of Safe Reuse
To safely reuse a *gorm.DB instance, use a New Session Method:
```
queryDB := DB.Where("name = ?", "jinzhu").Session(&gorm.Session{}) // First query queryDB.Where("age > ?", 10).First(&user) // SQL: SELECT * FROM users WHERE name = "jinzhu" AND age > 10 // Second query, safely isolated queryDB.Where("age > ?", 20).First(&user2) // SQL: SELECT * FROM users WHERE name = "jinzhu" AND age > 20
```
In this scenario, using Session(&gorm.Session{}) ensures that each query starts with a fresh context, preventing the pollution of SQL queries with conditions from previous operations. This is crucial for maintaining the integrity and accuracy of your database interactions.
**核心就是当你进行了一次链式操作，要注意这个db的db.clone字段已经为零，可以一些其他的方法去重置如session**
学习了，能下载[系统源码](http://doofuu.com/)吗，有go开发的[小程序案例](http://doofuu.com/article/4156206.html)吗，或者能[免费下载源码](https://free.doofuu.com)能也行
#1
今日阅读排行
一周阅读排行