Golang gorm 常用新增方法

酆恩

2023-12-01

前言

数据库连接&结构体定义

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Name     string `gorm:"type:varchar(30) not null"`
}

func (u User) TableName() string {
	return "user"
}

func getConnect() *gorm.DB {
	dsn := "root:Xrx@1994113@tcp(127.0.0.1:3306)/proxy?charset=utf8mb4&parseTime=True&loc=Local"
	open, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		//禁止创建外键
		DisableForeignKeyConstraintWhenMigrating: true,
	})
	if err != nil {
		panic(err)
	}
	return open
}

插入

create 返回 *gorm.DB，通过判断DB.Error判断插入是否出错,通过DB.RowsAffected 判断插入影响行数。插入成功的主键会写入到结构体中。

	db := getConnect()

	user := User{
		Name:     "xrx",
		Password: "199401134715",
	}
    
	result := db.Create(&user)

	if result.Error != nil {
		panic("插入错误")
	}

	fmt.Printf("影响条数:%d", result.RowsAffected)
	fmt.Printf("返回主键ID:%d", user.ID)

批量插入

create 支持传入结构体数组来实现批量插入

	db := getConnect()

	user := []User{
		{Name: "x1", Password: "x1"},
		{Name: "x2", Password: "x2"},
	}

	result := db.Select("Name").Create(&user)

	if result.Error != nil {
		panic("插入错误")
	}

	fmt.Printf("影响条数:%d", result.RowsAffected)

	for _, item := range user {
		fmt.Printf("返回主键ID:%d", item.ID)
	}

按批次插入

CreateInBatches(&[]结构体,int size)，和Create的批量插入没有什么区别，只是提供了按批次插入。要知道，批量插入的本质是将多条insert语句合成一条。由于mysql 的sql语句是有长度限制的。所以gorm提供了按批次来插入，来保证合成的sql语句长度不会超过限制。

	db := getConnect()

	user := []User{
		{Name: "x1", Password: "x1"},
		{Name: "x2", Password: "x2"},
	}

    //按批次插入，每个批次由10条数据构成
	result := db.CreateInBatches(&user, 10)

	if result.Error != nil {
		panic("插入错误")
	}

	fmt.Printf("影响条数:%d", result.RowsAffected)

	for _, item := range user {
		fmt.Printf("返回主键ID:%d", item.ID)
	}

关联保存(has one)

User在原有基础上增加了Company属性(代表一个用户拥有一个公司)，由于该属性是struct,并不会存入数据库中。Company需要提供一个属性来存放与User的关系(默认是struct名称 + 主键，即UserID)

type User struct {
	gorm.Model
	Name    string `gorm:"type:varchar(30) not null"`
	Company Company
}

func (u User) TableName() string {
	return "user"
}

type Company struct {
	gorm.Model
	UserId   int    `gorm:"type:int not null"`
	UserName string `gorm:"type:varchar(30) not null"`
	Name     string `gorm:"type:varchar(30) not null"`
}

func (u Company) TableName() string {
	return "compnay"
}

修改关联关系。改成user.name = company.user_name
foreignKey:UserName代表字段值写在company.user_name中
references:Name代表取Name值作为关联值，不填写默认取主键值作为

type User struct {
	gorm.Model
	Name    string `gorm:"type:varchar(30) not null"`
	Company Company `gorm:"foreignKey:UserName;references:Name"`
}

新增时，会先插入user，然再使用user提供的主键创建 company

func main() {
	connect := getConnect()
	user := User{
		Name: "php is best language",
		Company: Company{
			Name: "php company",
		},
	}
	result := connect.Create(&user)
	if err := result.Error; err != nil {
		panic(err)
	}

	fmt.Println(user.ID)
	fmt.Println(user.Company.ID)
}

关联保存 (has many)

这个例子实现一个用户拥有多个公司。用法和has one差不多。区别在于has one 的
User struct中Comapny 是 Comapny 类型，has many 中 Comapny 是 []Company 类型

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

type User struct {
	gorm.Model
	Name    string `gorm:"type:varchar(30) not null"`
	Company []Company
}

func (u User) TableName() string {
	return "user"
}

type Company struct {
	gorm.Model
	UserId   int    `gorm:"type:int not null"`
	UserName string `gorm:"type:varchar(30) not null"`
	Name     string `gorm:"type:varchar(30) not null"`
}

func (u Company) TableName() string {
	return "company"
}



func main() {
	connect := getConnect()
	user := User{
		Name: "user1",
		Company: []Company{
			{Name: "user1_company1"},
			{Name: "user1_company2"},
		},
	}
	result := connect.Create(&user)
	if err := result.Error; err != nil {
		panic(err)
	}

	fmt.Println(user.ID)
	for _, company := range user.Company {
		fmt.Println(company.ID)
	}
}

多对多关联保存 (many to many)

这里定义了一个用户可以拥有多个公司，一个公司也可以属于多个用户
many2many:user_company 是声明了中间表名：user_company，其中默认需要两个字段user_id(用户结构体名称 + 主键的蛇形),compnay_id(公司结构体名称 + 主键蛇形)。使用迁移AutoMigrate会自动创建该表(只有这两个字段,而且是联合主键)

type User struct {
	gorm.Model
	Name    string    `gorm:"type:varchar(30) not null"`
	Company []Company `gorm:"many2many:user_company;"`
}

func (u User) TableName() string {
	return "user"
}

type Company struct {
	gorm.Model
	Name     string `gorm:"type:varchar(30) not null"`
	User []User `gorm:"many2many:user_company;"`
}

func (u Company) TableName() string {
	return "company"
}

创建一个用户拥有多个公司的情况

	connect := getConnect()
    
    //一个公司对应多个用户 将User 换成 Company
	user := User{
		Name: "user1",
        //一个公司对应多个用户 将Company 换成 User
		Company: []Company{
			{Name: "company1"},
			{Name: "company2"},
		},
	}
	
	result := connect.Create(&user)
	if err := result.Error; err != nil {
		panic(err)
	}

	fmt.Println(user.ID)
	for _, company := range user.Company {
		fmt.Println(company.ID)
	}
    
    // INSERT INTO `company` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-14 18:21:04.597','2022-08-14 18:21:04.597',NULL,'company1'),('2022-08-14 18:21:04.597','2022-08-14 18:21:04.597',NULL,'company2') ON DUPLICATE KEY UPDATE `id`=`id`
    
    // INSERT INTO `user_company` (`user_id`,`company_id`) VALUES (1,1),(1,2) ON DUPLICATE KEY UPDATE `user_id`=`user_id`
    
   // INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-14 18:21:04.596','2022-08-14 18:21:04.596',NULL,'user1')

如果用户要绑定已存在的公司

	connect := getConnect()
    //公司绑定已存在用户，将User 替换成 Compnay
	user := User{
		Name: "user3",
        //公司绑定已存在用户 Company 替换成 User
		Company: []Company{
			{ID: 1},
			{ID: 2},
		},
	}

	result := connect.Create(&user)
	if err := result.Error; err != nil {
		panic(err)
	}

	fmt.Println(user.ID)
	for _, company := range user.Company {
		fmt.Println(company.ID)
	}
    
    //INSERT INTO `company` (`created_at`,`updated_at`,`deleted_at`,`name`,`id`) VALUES ('2022-08-15 00:11:08.269','2022-08-15 00:11:08.269',NULL,'',1),('2022-08-15 00:11:08.269','2022-08-15 00:11:08.269',NULL,'',2) ON DUPLICATE KEY UPDATE `id`=`id`

    // INSERT INTO `user_company` (`user_id`,`company_id`) VALUES (3,1),(3,2) ON DUPLICATE KEY UPDATE `user_id`=`user_id`

    //INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-15 00:11:08.268','2022-08-15 00:11:08.268',NULL,'user3')

替换关联表的字段，比如我将关联表user_company 中的user_id替换成uid,company_id替换成cid
foreignKey:关联值来源，从当前表中获取(User struct 中则为 user表)
joinForeignKey:在关联表中存储字段
References:关联值来源，从关联表中获取
JoinReferences:在关联表中存储字段

type User struct {
	gorm.Model
	Name    string    `gorm:"type:varchar(30) not null"`
	Company []Company `gorm:"many2many:user_company;foreignKey:ID;joinForeignKey:uid;References:ID;JoinReferences:cid"`
}

func (u User) TableName() string {
	return "user"
}

type Company struct {
	gorm.Model
	//ID   uint   `gorm:"primarykey"`
	Name string `gorm:"type:varchar(30) not null"`
	User []User `gorm:"many2many:user_company;foreignKey:ID;joinForeignKey:cid;References:ID;JoinReferences:uid"`
}

func (u Company) TableName() string {
	return "company"
}

零值(0,‘’,false)新增问题

模型定义了默认值:666666
下面这个插入是无法插入空值的

type User struct {
	gorm.Model
	Name string `gorm:"type:varchar(30) not null;default:666666"`
}

func (u User) TableName() string {
	return "user"
}

func main() {
	connect := getConnect()
	user := User{
		Name: "",
	}
	connect.Create(&user)
}

    //INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-15 18:06:09.01','2022-08-15 18:06:09.01',NULL,'666666')

通过更换Name 类型为 sql库提供的类型，可以插入零值

type User struct {
	gorm.Model
	Name sql.NullString `gorm:"type:varchar(30) not null;default:666666"`
}

func (u User) TableName() string {
	return "user"
}

func main() {
	connect := getConnect()
	user := User{
		Name: sql.NullString{
			String: "",
			Valid:  true,
		},
	}
	connect.Create(&user)
    //INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-15 18:13:55.033','2022-08-15 18:13:55.033',NULL,'')

}

通过更换Name 的类型为指针类型，可以插入零值

type User struct {
	gorm.Model
	Name *string `gorm:"type:varchar(30) not null;default:666666"`
}

func (u User) TableName() string {
	return "user"
}

func main() {
	connect := getConnect()
	empty := ""
	user := User{
		Name: &empty,
	}
	connect.Create(&user)
}

//INSERT INTO `user` (`created_at`,`updated_at`,`deleted_at`,`name`) VALUES ('2022-08-15 18:15:33.257','2022-08-15 18:15:33.257',NULL,'')

Golang gorm 常用新增方法

前言

插入

批量插入

按批次插入

关联保存(has one)

关联保存 (has many)

多对多关联保存 (many to many)

零值(0,‘’,false)新增问题

相关阅读

相关文章

相关问答

相关文档