linux
go env -w GOPROXY=https://goproxy.cn,direct
或
export GOPROXY=https://goproxy.cn
echo "export GOPROXY=https://goproxy.cn" >> ~/.profile && source ~/.profile
下载所有依赖包
go mod tidy
go mod init github/caoxiaojin/gorm_class
GOPROXY=https://goproxy.cn
https://learnku.com/docs/gorm/v2/update/9734
go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql一、表操作
package main
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/schema"
"time"
)
func main() {
//dns := "db1:db123456@tcp(192.168.85.128:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local"
//db,err := gorm.Open(mysql.Open(dns),&gorm.Config{})
db,err :=gorm.Open(mysql.New(mysql.Config{
DSN: "db1:db123456@tcp(192.168.85.128:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local",
DefaultStringSize: 171,
}),&gorm.Config{
SkipDefaultTransaction: false,
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", //表明前缀 `User` 的表名 应该是 `t_users`
SingularTable: false, // 使用单数表名，启用 表示 `t_user`
},
DisableForeignKeyConstraintWhenMigrating: true, // 逻辑外键
})
type User struct {
Name string
}
sqlDB,_ := db.DB()
sqlDB.SetMaxIdleConns(10) // 连接池最大的空闲连接数
sqlDB.SetMaxOpenConns(100) // 连接池最多容纳的链接数量
sqlDB.SetConnMaxLifetime(time.Hour) // 链接池中链接的最大可复用时间
// 建表一
_ =db.AutoMigrate(&User{})
// 建表二
M := db.Migrator()
//M.CreateTable(&User{})
// 查看表是否存在
fmt.Println(M.HasTable(&User{}))
fmt.Println(M.HasTable("t_users"))
// 删除表
//fmt.Println(M.DropTable(&User{}))
// 重命名表
M.RenameTable(&User{},"t_users_old")
fmt.Println(db,err)
}二、常规表信息设计
package main
import (
"database/sql"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/schema"
"time"
)
var GLOBL_DB *gorm.DB
func main() {
db,_ :=gorm.Open(mysql.New(mysql.Config{
DSN: "db1:db123456@tcp(192.168.85.128:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local",
DefaultStringSize: 171,
}),&gorm.Config{
SkipDefaultTransaction: false,
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", //表明前缀 `User` 的表名 应该是 `t_users`
SingularTable: false, // 使用单数表名，启用 表示 `t_user`
},
DisableForeignKeyConstraintWhenMigrating: true, // 逻辑外键
})
sqlDB,_ := db.DB()
sqlDB.SetMaxIdleConns(10) // 连接池最大的空闲连接数
sqlDB.SetMaxOpenConns(100) // 连接池最多容纳的链接数量
sqlDB.SetConnMaxLifetime(time.Hour) // 链接池中链接的最大可复用时间
GLOBL_DB = db
Testusercreate()
}
func Testusercreate() {
GLOBL_DB.AutoMigrate(&Testuser{})
}
type Model struct {
UUID uint `gorm:"primaryKey"`
Time time.Time `gorm:"column:my_time"`
}
type Testuser struct {
Model Model `gorm:"embedded;embeddedPrefix:qm_"`
Name string `gorm:"default:qm"` // 设置默认值
Email *string `gorm:"not null"` // 不能为空
Age uint8 `gorm:"comment:年龄"`
Birthday *time.Time
ActiveAT sql.NullTime
CreateAt time.Time
UpdateAt time.Time
}三、单表的 增删改查
1）增
type Testuser struct {
gorm.Model
Name string `gorm:"default:qm"` // 设置默认值
Age uint8 `gorm:"comment:年龄"`
}
func ActionCreate() {
GLOBL_DB.Create(&Testuser{Name: "张三",Age: 18})
GLOBL_DB.Select("Name").Create(&Testuser{Name: "里斯",Age: 19}) // 只创建 Name 字段
GLOBL_DB.Omit("Name").Create(&Testuser{Name: "里斯",Age: 19}) // 除了 Name 字段
GLOBL_DB.Create(&[]TestUser{
{Name: "张三",Age: 18},
{Name: "张三",Age: 18},
{Name: "张三",Age: 18},
{Name: "张三",Age: 18},
})
}2）查
func ActionSelect() {
var result = make(map[string]interface{})
GLOBL_DB.Model(&Testuser{}).First(&result)
fmt.Println(result)
var User Testuser
GLOBL_DB.Model(&Testuser{}).First(&User)
fmt.Println(User)
GLOBL_DB.Model(&Testuser{}).Take(&User)
fmt.Println(User)
GLOBL_DB.Model(&Testuser{}).Last(&User)
fmt.Println(User)
// 主键检索
dbRes := GLOBL_DB.Model(&Testuser{}).First(&User,10)
fmt.Println(errors.Is(dbRes.Error,gorm.ErrRecordNotFound))
// 条件查询
GLOBL_DB.Where("name = ?","qm").First(&User)
GLOBL_DB.Where("name = ? AND age = 21","gm").First(&User)
GLOBL_DB.Where(map[string]interface{}{
"name":"gm",
}).First(&User)
GLOBL_DB.Where("name = ?","gm").Or("age = ?",11).First(&User)
GLOBL_DB.Where(Testuser{Name: "gm"}).First(&User)
GLOBL_DB.First(&User,"name = ?","gm")
GLOBL_DB.First(&User,map[string]interface{}{"name":"gm"})
GLOBL_DB.First(&User,Testuser{Name: "gm"})
// 查询多条数据
var Users []Testuser
GLOBL_DB.Find(&Users)
GLOBL_DB.Where("name LIKE ?","%gm%").Find(&Users)
GLOBL_DB.Select("name").Where("name LIKE ?","%gm%").Find(&Users) // 只要name字段
GLOBL_DB.Omit("name").Where("name LIKE ?","%gm%").Find(&Users) // 不要name字段
// 提前定义接收的字段
var u []Userinfo
GLOBL_DB.Model(&Testuser{}).Where("name LIKE ?","%gm%").Find(&u)
}
type Userinfo struct {
NAME string
Age uint8
}3）更新
func ActionUpdate () {
// Update 只更新选择的字段
// Updates 更新所有字段，此时有2种形式，一种为Map,一种为结构体，结构体零值不参与更新
// save 无论如何都更新，所有内容包括 0 值
GLOBL_DB.Model(&Testuser{}).Where("name LIKE ?","%gm%").Update("name","lisi")
var users []Testuser
dbRes := GLOBL_DB.Model(&Testuser{}).Where("name LIKE ?","%gm%").Find(&users)
for k:= range users {
users[k].Age = 18
}
dbRes.Save(&users)
var user Testuser
GLOBL_DB.First(&user).Updates(Testuser{Name: "",Age: 0}) // 不参与更新
GLOBL_DB.First(&user).Updates(map[string]interface{}{"Name":"","Age":0}) // 参与更新
// 批量更新
GLOBL_DB.Find(&users).Updates(map[string]interface{}{"Name":"","Age":0})
}删除
func Actiondel() {
var users []Testuser
GLOBL_DB.Where("name = ?","gm").Delete(&users) // 软删除
GLOBL_DB.Unscoped().Where("name = ?","gm").Delete(&users) // 硬删除
}原生sql
func ActionSql() {
var users []Testuser
GLOBL_DB.Raw("SELECT * FROM users WHERE name = ?","qm").Scan(&users)
}或查询
db.Where("name = ? OR age > ?", "Alice", 18).Find(&users)
db.Where("name = ?", "Alice").Or("age > ?", 18).Find(&users)
db.Where("name = ?", "Alice").Or(db.Where("age > ?", 18).Where("gender = ?", "female")).Find(&users)四、关联查询之 一对一关联
1）一对一关联：
注意初始化表一张表就可以了。关联数据会被关联创建
// belongsTO
func One2one() {
GLOBL_DB.AutoMigrate(&Dog{})
g:=GirlGod{
Model:gorm.Model{
ID: 1,
},
Name: "琪琪",
}
d := Dog{
Model:gorm.Model{
ID: 1,
},
Name: "张三",
GirlGod: g,
}
GLOBL_DB.Create(&d)
}
type Dog struct {
gorm.Model
Name string
GirlGodID uint
GirlGod GirlGod
}
type GirlGod struct {
gorm.Model
Name string
}1.1）关联查询。Preload
func Find() {
var dog Dog
GLOBL_DB.Preload("GirlGod").Find(&dog,2)
fmt.Println(dog)
}
type Dog struct {
gorm.Model
Name string
GirlGodID uint
GirlGod GirlGod
}
type GirlGod struct {
gorm.Model
Name string
}1.2）关联操作
func One2one () {
d:=Dog{
Model:gorm.Model{
ID: 1,
},
}
g:=GirlGod{
Model:gorm.Model{
ID: 1,
},
}
g2:=GirlGod{
Model:gorm.Model{
ID: 1,
},
}
//创建关联
GLOBL_DB.Model(&d).Association("GirlGod").Append(&g)
//删除关联
GLOBL_DB.Model(&d).Association("GirlGod").Delete(&g)
//更改关联
GLOBL_DB.Model(&d).Association("GirlGod").Replace(&g,&g2)
// 清理所有的关系
GLOBL_DB.Model(&d).Association("GirlGod").Clear()
}2）一对一关联的另一种写法。初始化2张表。并创建数据
package main
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/schema"
"time"
)
var GLOBL_DB *gorm.DB
func main() {
db,_ :=gorm.Open(mysql.New(mysql.Config{
DSN: "db1:db123456@tcp(192.168.85.128:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local",
DefaultStringSize: 171,
}),&gorm.Config{
SkipDefaultTransaction: false,
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", //表明前缀 `User` 的表名 应该是 `t_users`
SingularTable: false, // 使用单数表名，启用 表示 `t_user`
},
DisableForeignKeyConstraintWhenMigrating: true, // 逻辑外键
})
sqlDB,_ := db.DB()
sqlDB.SetMaxIdleConns(10) // 连接池最大的空闲连接数
sqlDB.SetMaxOpenConns(100) // 连接池最多容纳的链接数量
sqlDB.SetConnMaxLifetime(time.Hour) // 链接池中链接的最大可复用时间
GLOBL_DB = db
One2one()
}
// belongsTO
func One2one() {
GLOBL_DB.AutoMigrate(&GirlGod{},&Dog{})
d := Dog{
Name: "李四",
}
g:=GirlGod{
Name: "美眉",
Dog: d,
}
GLOBL_DB.Create(&g)
}
type Dog struct {
gorm.Model
Name string
GirlGodID uint
}
type GirlGod struct {
gorm.Model
Name string
Dog Dog
}View Code
2.1) 查询。通过Preload 预加载把关联数据拿出来
func Find() {
var girl GirlGod
GLOBL_DB.Preload("Dog").Find(&girl,2)
fmt.Println(girl)
}五、一对多关联
1）数据创建
type Dog struct {
gorm.Model
Name string
GirlGodID uint
}
type GirlGod struct {
gorm.Model
Name string
Dogs []Dog
}
func One2one () {
GLOBL_DB.AutoMigrate(&Dog{},&GirlGod{})
d1 := Dog{
Model:gorm.Model{
ID: 1,
},
Name: "1号",
}
d2 := Dog{
Model:gorm.Model{
ID: 2,
},
Name: "2号",
}
g := GirlGod{
Model:gorm.Model{
ID: 1,
},
Name: "美女",
Dogs: []Dog{d1,d2},
}
GLOBL_DB.Create(&g)
}1.1) 查询
func Find() {
var girl GirlGod
// 查询所有的关系数据
GLOBL_DB.Preload("Dogs").First(&girl)
// 查询关系中携带条件
GLOBL_DB.Preload("Dogs","name = ?","1号").First(&girl)
// 查询关系中携带条件,条件可以是函数
GLOBL_DB.Preload("Dogs", func(db *gorm.DB) *gorm.DB {
return db.Where("name = ?","1号")
}).First(&girl)
fmt.Println(girl)
}2）3张表，一对多，多对一关系查询
type Info struct {
gorm.Model
Money int
DogID uint
}
type Dog struct {
gorm.Model
Name string
GirlGodID uint
Info Info
}
type GirlGod struct {
gorm.Model
Name string
Dogs []Dog
}
func Find() {
var girl GirlGod
// 链式关联查询
GLOBL_DB.Preload("Dogs.Info").First(&girl)
// 链式关联查询 加条件查询
GLOBL_DB.Preload("Dogs.Info").Preload("Dogs","name = ?","1号").First(&girl)
GLOBL_DB.Preload("Dogs.Info","money > 100").Preload("Dogs","name = ?","1号").First(&girl)
// joins的用法
GLOBL_DB.Preload("Dogs", func(db *gorm.DB) *gorm.DB {
return db.Joins("Info").Where("money > 200")
}).First(&girl)
}六、多对多关联
1）创建多个数据
type Info struct {
gorm.Model
Money int
DogID uint
}
type Dog struct {
gorm.Model
Name string
Info Info
GirlGods []GirlGod `gorm:"many2many:dog_girl_god"`
}
type GirlGod struct {
gorm.Model
Name string
Dogs []Dog `gorm:"many2many:dog_girl_god"`
}
func Many2many () {
GLOBL_DB.AutoMigrate(&Dog{},&GirlGod{},&Info{})
i := Info{
Money: 2000,
}
g1 := GirlGod{
Name: "美女1号",
}
g2 := GirlGod{
Name: "美女2号",
}
d:= Dog{
Name: "狗子1号",
GirlGods: []GirlGod{g1,g2},
Info: i,
}
GLOBL_DB.Create(&d)
}
2) 查询与关系维护
func Find() {
d:=Dog{
Model:gorm.Model{
ID: 1,
},
}
// 查看 Dog 包含下面的GirlGods信息
GLOBL_DB.Preload("GirlGods").Find(&d)
fmt.Println(d)
// 只需要 Dog下面的GirlGods信息
var girls []GirlGod
GLOBL_DB.Model(&d).Association("GirlGods").Find(&girls)
fmt.Println(girls)
// 查看 girls包含下面的Dog信息
GLOBL_DB.Model(&d).Preload("Dogs").Association("GirlGods").Find(&girls)
GLOBL_DB.Model(&d).Preload("Dogs.Info").Association("GirlGods").Find(&girls)
}
func guanxi() {
GLOBL_DB.Model(&d).Association("GirlGods").Append(&g1,&g2)
GLOBL_DB.Model(&d).Association("GirlGods").Delete(&g2)
GLOBL_DB.Model(&d).Association("GirlGods").Replace(&g2)
GLOBL_DB.Model(&d).Association("GirlGods").Replace(&g2,g1)
GLOBL_DB.Model(&d).Association("GirlGods").Clear()
}七、多态的使用
1）构建数据
type Jiazi struct {
ID uint
Name string
Xiaofengche Xiaofengche `gorm:"polymorphic:Owner"`
}
type Yujie struct {
ID uint
Name string
Xiaofengche Xiaofengche `gorm:"polymorphic:Owner"`
}
type Xiaofengche struct {
ID uint
Name string
OwnerType string
OwnerID uint
}
func Many2many () {
GLOBL_DB.AutoMigrate(&Jiazi{},&Yujie{},&Xiaofengche{})
GLOBL_DB.Create(&Jiazi{Name: "夹子",Xiaofengche: Xiaofengche{Name: "小风车"}})
GLOBL_DB.Create(&Yujie{Name: "御姐",Xiaofengche: Xiaofengche{Name: "大风车"}})
}
可自定义类型字段
列如
type Jiazi struct {
ID uint
Name string
Xiaofengche Xiaofengche `gorm:"polymorphic:Owner;polymorphicValue:huhu"`
}
2）多态也支持一对多
type Jiazi struct {
ID uint
Name string
Xiaofengche []Xiaofengche `gorm:"polymorphic:Owner;polymorphicValue:huhu"`
}
type Xiaofengche struct {
ID uint
Name string
OwnerType string
OwnerID uint
}
func Many2many () {
GLOBL_DB.Create(&Jiazi{Name: "夹子",Xiaofengche: []Xiaofengche{
{Name: "小风车1"},
{Name: "小风车2"},
}})
}八、外键关联
// foreignKey 默认引用id
// references 重置引用
type Jiazi struct {
ID uint
Name string
Xiaofengche []Xiaofengche `gorm:"foreignKey:JiaziName;references:Name"`
}
type Xiaofengche struct {
ID uint
Name string
JiaziName string
}
func Many2many () {
GLOBL_DB.AutoMigrate(&Jiazi{},&Xiaofengche{})
GLOBL_DB.Create(&Jiazi{Name: "夹子",Xiaofengche: []Xiaofengche{
{Name: "小风车1"},
{Name: "小风车2"},
}})
}View Code
九、many2many的外键关联
// foreignKey 默认引用id
// references 重置引用
type Jiazi struct {
ID uint
Name string
Xiaofengche []Xiaofengche `gorm:"many2many:jiazi_fengche;foreignKey:Name;references:FCName"`
}
type Xiaofengche struct {
ID uint
FCName string
JiaziName string
}
func Many2many () {
GLOBL_DB.AutoMigrate(&Jiazi{},&Xiaofengche{})
GLOBL_DB.Create(&Jiazi{Name: "夹子",Xiaofengche: []Xiaofengche{
{FCName: "小风车1"},
{FCName: "小风车2"},
}})
}扩展
// foreignKey 默认引用id
// references 重置引用
type Jiazi struct {
ID uint
Name string
Xiaofengche []Xiaofengche `gorm:"many2many:jiazi_fengche;foreignKey:Name;references:FCName;joinReferences:fengche"`
}
type Xiaofengche struct {
ID uint
FCName string
JiaziName string
}
func Many2many () {
GLOBL_DB.AutoMigrate(&Jiazi{},&Xiaofengche{})
GLOBL_DB.Create(&Jiazi{Name: "夹子",Xiaofengche: []Xiaofengche{
{FCName: "小风车1"},
{FCName: "小风车2"},
}})
}View Code
标签
many2many:jiazi_fengche;
foreignKey:Name;
references:FCName;
joinReferences:fengche八、事务的使用
1）使用 Transaction 来提交事务
func TestTransaction(){
flag := false
GLOBL_DB.AutoMigrate(&TMG{})
GLOBL_DB.Transaction(func(tx *gorm.DB) error {
tx.Create(&TMG{Name: "汉字"})
tx.Create(&TMG{Name: "汉字"})
tx.Create(&TMG{Name: "汉字"})
if flag {
return nil
} else {
return errors.New("有汉字")
}
})
}备注
1）使用 Transaction 开启事务
2）内部需要使用func函数来写执行的sql
3）func函数需要有返回值，nil 返回则执行sql语句生成在数据库，errors不会生成数据库数据2）事务的嵌套使用
func TestTransaction1(){
GLOBL_DB.AutoMigrate(&TMG{})
GLOBL_DB.Transaction(func(tx *gorm.DB) error {
tx.Create(&TMG{Name: "一"})
tx.Create(&TMG{Name: "二"})
tx.Transaction(func(tx *gorm.DB) error {
tx.Create(&TMG{Name: "三"})
return errors.New("有汉字")
})
return nil
})
}3）Begin 开启事务，Commit提交事务，Rollback 回滚，提交的事务不会被执行
func TestTransaction2(){
GLOBL_DB.AutoMigrate(&TMG{})
tx := GLOBL_DB.Begin()
tx.Create(&TMG{Name: "一"})
tx.Create(&TMG{Name: "aa"})
if true {
// 回滚
tx.Rollback()
}
// 有了 Rollback。Commit已经没有用了
tx.Commit()
}4）SavePoint 与 RollbackTo的使用
func TestTransaction3(){
GLOBL_DB.AutoMigrate(&TMG{})
tx := GLOBL_DB.Begin()
tx.Create(&TMG{Name: "一"})
tx.Create(&TMG{Name: "aa"})
// 忽略 SavePoint 到 RollbackTo 中间的代码
tx.SavePoint("duo")
tx.Create(&TMG{Name: "bb"})
tx.RollbackTo("dop")
tx.Commit()
}九、自定义数据类型
1）数据库存json。通过 value存到数据库，通过Scan从数据库解析出来
type CInfo struct {
Name string
Age int
}
func (c CInfo)Value() (driver.Value,error) {
str,err := json.Marshal(c)
if err != nil {
return nil, err
}
return string(str),nil
}
func (c *CInfo)Scan(value interface{}) (error) {
str,ok := value.([]byte)
if !ok {
return errors.New("不匹配的数据类型")
}
json.Unmarshal(str,c)
return nil
}
type Cuser struct {
ID int
Info CInfo
}
func Make(){
GLOBL_DB.AutoMigrate(&Cuser{})
GLOBL_DB.Create(&Cuser{Info:CInfo{Name: "哈哈",Age: 22}})
}
func Find() {
var u Cuser
GLOBL_DB.First(&u)
fmt.Println(u)
}9.1) 字符串拼接
package main
import (
"database/sql/driver"
"errors"
"strings"
)
type CInfo struct {
Name string
Age int
}
type CUser struct {
ID uint
Info CInfo `gorm:"type:text"`
Args Args
}
type Args []string
//存储 1,2,3,4
func (self Args) Value() (driver.Value, error) {
if len(self) > 0 {
var str string = self[0]
for _, v := range self[1:] {
str += "," + v
}
return str, nil
} else {
return "", nil
}
}
func (self *Args) Scan(value interface{}) error {
str, ok := value.([]byte)
if !ok {
return errors.New("数据类型无法解析")
}
*self = strings.Split(string(str), ",")
return nil
}
func main() {
}View Code
十、导入sql
package main
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"io/ioutil"
"strings"
)
var (
db_cmdb *gorm.DB
)
func gormMysql(tableDb string) {
connstr := fmt.Sprintf("root:123456@tcp(192.168.85.123:3306)/%s?charset=utf8mb4&parseTime=true", tableDb)
mysqlConfig := mysql.Config{
DSN: connstr, // DSN data source name
DefaultStringSize: 191, // string 类型字段的默认长度
SkipInitializeWithVersion: false, // 根据版本自动配置
}
if db, err := gorm.Open(mysql.New(mysqlConfig), &gorm.Config{
SkipDefaultTransaction: false,
DisableForeignKeyConstraintWhenMigrating: true, // 逻辑外键
}); err != nil {
return
} else {
sqlDB, _ := db.DB()
sqlDB.SetMaxIdleConns(10)
sqlDB.SetMaxOpenConns(100)
db_cmdb = db
}
}
}
func readfile() {
sqls, _ := ioutil.ReadFile("futong_cmp_cmdb.sql")
sqlArr := strings.Split(string(sqls), ";")
for _, sql := range sqlArr {
sql = strings.TrimSpace(sql)
if sql == "" {
continue
}
err := db_cmdb.Exec(sql).Error
if err != nil {
fmt.Println(err)
return
} else {
fmt.Println(sql, " success")
}
}
}
func main() {
gormMysql("cmdb")
readfile()
}View Code
查询
func (self *modelDataRepository) mysqlData(tableDB string, modelData common.ModelDataList) (schema []map[string]interface{}, count int64, err error) {
db := func(db *gorm.DB) *gorm.DB {
for key, value := range modelData.SearchMap {
db.Where(fmt.Sprintf("%s LIKE ?", key), fmt.Sprintf("%%%s%%", value))
}
return db
}
tx := global.MapDB[tableDB].Begin()
tx.Scopes(db).Table(modelData.ModelName).Offset(int(modelData.Current - 1)).Limit(int(modelData.Size)).Find(&schema)
tx.Scopes(db).Table(modelData.ModelName).Count(&count)
return schema, count, err
}begin查询更新
tx := DB.Begin()
tx = tx.Where("project_code = ?",config.ProjectCode)
tx = tx.Where("environment = ?",config.Environment)
tx = tx.Where("service = ?",config.Service)
tx = tx.Where("filename = ?",config.Filename)
tx.Model(model.KerriganConfig{}).Update("content",config.Content)
tx.Commit()十一、json字段内部查询，待验证测试
type Book struct {
ID uint `gorm:"primary_key"`
Title string `gorm:"size:100;not null"`
Author string `gorm:"size:100;not null"`
Metadata map[string]interface{} `gorm:"type:jsonb;not null"`
}
在 tag 中，我们使用了 "type:jsonb"，表示这是一个 json 字段。我们需要在查询时指定 json 中的 key，可以通过下面的方式来实现：
db.Where("metadata->>'$.language'=?", "en").Find(&books)
上面的代码使用了 "->>" 操作符，表示从 json 中获取一个字符串值。其中，".language" 是 json 中的 key 名称，"en" 是需要匹配的字符串值。
-----------------------------------------------------------
如果需要匹配的不是字符串，而是数字或布尔值，可以使用 "->" 操作符：
db.Where("metadata->'$.year' BETWEEN ? and ?", 1950, 2000).Find(&books)
上面的代码可以查询出 metadata 中 "year" key 字段的值在 1950 到 2000 之间的 Book 记录。json 字段
package resource
import (
"fiy/common/models"
"gorm.io/datatypes"
)
/*
@Author : lanyulei
*/
// 字段数据
type Data struct {
Id int `gorm:"column:id; primary_key;AUTO_INCREMENT" json:"id"` // 字段分组ID
Uuid string `gorm:"column:uuid; type:varchar(45); unique;" json:"uuid" binding:"required"` // 设备唯一ID
InfoId int `gorm:"column:info_id; type:int(11); index;" json:"info_id" binding:"required"` // 对应的模型ID
InfoName string `gorm:"column:info_name; type:varchar(128);" json:"info_name" binding:"required"` // 对应的模型名称
Status int `gorm:"column:status; type:int(11); default:1" json:"status"` // 0 没有状态，1 空闲，2 故障，3 待回收，4 正在使用
Data datatypes.JSON `gorm:"column:data; type:json" json:"data" binding:"required"` // 数据
models.BaseModel
}
func (Data) TableName() string {
return "cmdb_resource_data"
}json字段已验证
package main
import (
"database/sql/driver"
"encoding/json"
"fmt"
"gorm.io/datatypes"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/schema"
"time"
)
type XTime struct {
time.Time
}
// 1. 为 Xtime 重写 MarshaJSON 方法，在此方法中实现自定义格式的转换；
func (t XTime) MarshalJSON() ([]byte, error) {
output := fmt.Sprintf("\"%s\"", t.Format("2006-01-02 15:04:05"))
return []byte(output), nil
}
// 2. 为 Xtime 实现 Value 方法，写入数据库时会调用该方法将自定义时间类型转换并写入数据库；
func (t XTime) Value() (driver.Value, error) {
var zeroTime time.Time
if t.Time.UnixNano() == zeroTime.UnixNano() {
return nil, nil
}
return t.Time, nil
}
// 3. 为 Xtime 实现 Scan 方法，读取数据库时会调用该方法将时间数据转换成自定义时间类型；
func (t *XTime) Scan(v interface{}) error {
value, ok := v.(time.Time)
if ok {
*t = XTime{Time: value}
return nil
}
return fmt.Errorf("can not convert %v to timestamp", v)
}
type ServicePrototype struct {
Id int `gorm:"column:id; primary_key;AUTO_INCREMENT;comment:唯一id" json:"id"`
Name string `gorm:"column:name; type:varchar(128);unique;comment:名称" json:"name" v:"required"`
UserName string `gorm:"column:username; type:varchar(128);comment:创建人" json:"username"`
Data datatypes.JSON `gorm:"column:data; type:json" json:"data" binding:"required"`
}
var DB *gorm.DB
func init() {
if db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:m9uSFL7duAVXfeAwGUSG@tcp(192.168.85.123:3306)/ft_cmdb?charset=utf8mb4&parseTime=True&loc=Local",
DefaultStringSize: 171,
}), &gorm.Config{
SkipDefaultTransaction: false,
NamingStrategy: schema.NamingStrategy{
TablePrefix: "t_", //表明前缀 `User` 的表名 应该是 `t_users`
SingularTable: false, // 使用单数表名，启用 表示 `t_user`
},
DisableForeignKeyConstraintWhenMigrating: true, // 逻辑外键
}); err != nil {
panic(err.Error())
} else {
DB = db
sqlDB, _ := DB.DB()
sqlDB.SetMaxIdleConns(10) // 连接池最大的空闲连接数
sqlDB.SetMaxOpenConns(100) // 连接池最多容纳的链接数量
sqlDB.SetConnMaxLifetime(time.Hour) // 链接池中链接的最大可复用时间
}
if err := DB.AutoMigrate(&ServicePrototype{}); err != nil {
fmt.Println(err)
}
}
func insert() {
data := make(map[string]interface{})
data["age"] = 13
data["city"] = "上海"
newdata, _ := json.Marshal(&data)
servicePrototype := ServicePrototype{Name: "zhangsan1", UserName: "admin1", Data: newdata}
DB.Create(&servicePrototype)
}
func find() {
var servicePrototype ServicePrototype
DB.Where("data->>'$.age'=?", 13).Find(&servicePrototype)
fmt.Println(servicePrototype.Data)
fmt.Println(servicePrototype.Name)
}
func main() {
find()
}View Code
存储切片
var servicePrototype ServicePrototype
DB.Where("name = ?", "zhangsan2").Where("data->>'$[0].age'=?", 13).Find(&servicePrototype)切片查询
MySQL [ft_cmdb]> select * from t_service_prototypes where JSON_CONTAINS(data,JSON_OBJECT('age', 14));
+----+-----------+----------+----------------------------------------------------------------+
| id | name | username | data |
+----+-----------+----------+----------------------------------------------------------------+
| 1 | zhangsan | admin | [{"age": 12, "city": "北京"}, {"age": 14, "city": "上海"}] |
| 3 | zhangsan1 | admin1 | [{"age": 15, "city": "杭州"}, {"age": 14, "city": "重庆"}] |
+----+-----------+----------+----------------------------------------------------------------+对应的go代码
var servicePrototype []ServicePrototype
DB.Where("JSON_CONTAINS(data, JSON_OBJECT('age', ?))", 14).Find(&servicePrototype)设置表名描述
type User struct {
gorm.Model
Name string `gorm:"comment:用户姓名"`
Age int `gorm:"comment:用户年龄"`
Email string `gorm:"comment:用户邮箱"`
}
type Product struct {
gorm.Model
Name string `gorm:"comment:产品名称"`
Price float64 `gorm:"comment:产品价格"`
}
func main() {
db, err := gorm.Open("mysql", "your-database-connection-string")
if err != nil {
// 处理数据库连接错误
}
defer db.Close()
// 设置表描述信息
db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'").AutoMigrate(&User{})
db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品信息表'").AutoMigrate(&Product{})
}View Code
设置表名描述优化
import (
"gorm.io/gorm"
"gorm.io/gorm/clause"
)
type User struct {
gorm.Model
Name string `gorm:"comment:用户姓名"`
Age int `gorm:"comment:用户年龄"`
Email string `gorm:"comment:用户邮箱"`
}
type Product struct {
gorm.Model
Name string `gorm:"comment:产品名称"`
Price float64 `gorm:"comment:产品价格"`
}
func main() {
db, err := gorm.Open("mysql", "your-database-connection-string")
if err != nil {
// 处理数据库连接错误
}
defer db.Close()
// 定义表描述信息
userTableOptions := clause.TableOption{
Option: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'",
}
productTableOptions := clause.TableOption{
Option: "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='产品信息表'",
}
// 迁移模型并设置表描述信息
db.AutoMigrate(&User{}, &Product{}, &gorm.Model{},
&clause.Table{Name: "users", TableOption: userTableOptions},
&clause.Table{Name: "products", TableOption: productTableOptions},
)
}View Code
表名优化
func (Order) TableOptions() string {
return "engine=innodb comment='订单表'"
}
if t, ok := t.(database.TableOptions); ok {
defTableOpts = t.TableOptions()
}
循环执行 .Set("gorm:table_options", defTableOpts)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
