GORM常用的插入

时间：2025-02-21 08:36:41

创建记录

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)

type User struct {
	ID           uint
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
}

func main() {
	// 连接对应的数据库
	dsn := "root:root@tcp(192.168.193.128:3306)/grom_test?charset=utf8mb4&parseTime=True&loc=Local"
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 日志级别
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 使用用彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: newLogger})
	if err != nil {
		panic(err)
	}
	birthday := time.Now()
	user := User{Name: "Jinzhu", Age: 18, Birthday: &birthday}

	result := db.Create(&user) // 通过数据的指针来创建

	// 1
	fmt.Println(user.ID)             // 返回插入数据的主键
	// <nil>
	fmt.Println(result.Error)        // 返回 error
	// 1
	fmt.Println(result.RowsAffected) // 返回插入记录的条数
}

日志:

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('Jinzhu',NULL,18,'2022-05-26 16:34:40.986',NULL,NULL,'2022-05-26 16:34:40.988','2022-05-26 16:34:40.988')

用指定的字段创建记录

创建记录并更新给出的字段

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	birthday := time.Now()
	user := User{Name: "Jinzhu", Age: 18, Birthday: &birthday}
	db.Select("Name", "Age", "CreatedAt").Create(&user) // 创建记录并更新给出的字段。
}

日志:

INSERT INTO `users` (`name`,`age`,`created_at`,`updated_at`) VALUES ('Jinzhu',18,'2022-05-26 16:39:00.906','2022-05-26 16:39:00.906')

创建一个记录且一同忽略传递给略去的字段值。

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	birthday := time.Now()
	user := User{Name: "Jinzhu", Age: 18, Birthday: &birthday}
	db.Omit("Name", "Age", "CreatedAt").Create(&user) // 创建一个记录且一同忽略传递给略去的字段值。
}

日志:

INSERT INTO `users` (`email`,`birthday`,`member_number`,`activated_at`,`updated_at`) VALUES (NULL,'2022-05-26 16:39:00.904',NULL,NULL,'2022-05-26 16:39:00.906')

创建钩子

GORM 允许用户定义的钩子有 BeforeSave, BeforeCreate, AfterSave, AfterCreate 创建记录时将调用这些钩子方法，请参考 Hooks中关于生命周期的详细信息

func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
  u.UUID = uuid.New()

    if u.Role == "admin" {
        return errors.New("invalid role")
    }
    return
}

如果想跳过 钩子 方法，可以使用 SkipHooks 会话模式，例如：

DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)

DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)

DB.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(users, 100)

批量插入

要有效地插入大量记录，请将一个 slice 传递给 Create 方法。 GORM 将生成单独一条SQL语句来插入所有数据，并回填主键的值，钩子方法也会被调用。

一次性插入

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
	db.Create(&users)

	for _, user := range users {
		// 5 6 7
		fmt.Println(user.ID)
	}
}

日志:

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu1',NULL,0,NULL,NULL,NULL,'2022-05-26 17:09:15.298','2022-05-26 17:09:15.298'),('jinzhu2',NULL,0,NULL,NULL,NULL,'2022-05-26 17:09:15.298','2022-05-26 17:09:15.298'),('jinzhu3',NULL,0,NULL,NULL,NULL,'2022-05-26 17:09:15.298','2022-05-26 17:09:15.298')

分批插入

SQL语句的长度是有限制的;如果一次性插入的数据量过大,有可能会执行不了;可以采用分批插入;

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
	// 每两条插入一次
	db.CreateInBatches(users, 2)
	for _, user := range users {
	 	// 8 9 10
		fmt.Println(user.ID)
	}
}

日志:

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu1',NULL,0,NULL,NULL,NULL,'2022-05-26 17:11:49.606','2022-05-26 17:11:49.606'),('jinzhu2',NULL,0,NULL,NULL,NULL,'2022-05-26 17:11:49.606','2022-05-26 17:11:49.606')

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu3',NULL,0,NULL,NULL,NULL,'2022-05-26 17:11:49.608','2022-05-26 17:11:49.608')

根据 Map 创建

GORM 支持根据 map[string]interface{} 和 []map[string]interface{}{} 创建记录;
注意: 根据 map 创建记录时，association 不会被调用，且主键也不会自动填充

插入单条

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	db.Model(&User{}).Create(map[string]interface{}{
		"Name": "jinzhu", "Age": 18,
	})
}

日志:

INSERT INTO `users` (`age`,`name`) VALUES (18,'jinzhu')

批量插入

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	db.Model(&User{}).Create([]map[string]interface{}{
		{"Name": "jinzhu_1", "Age": 18},
		{"Name": "jinzhu_2", "Age": 20},
	})
}

日志:

INSERT INTO `users` (`age`,`name`) VALUES (18,'jinzhu_1'),(20,'jinzhu_2')

关联创建

关联创建

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
type CreditCard struct {
	gorm.Model
	Number   string
	UserID   uint
}

type User struct {
	ID           uint
	Name         string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
	CreditCard CreditCard
}

func main() {
	// 省略连接数据库代码
	db.Create(&User{
		Name: "jinzhu",
		CreditCard: CreditCard{Number: "411111111111"},
	})
}

日志:

INSERT INTO `credit_cards` (`created_at`,`updated_at`,`deleted_at`,`number`,`user_id`) VALUES ('2022-05-26 17:21:24.763','2022-05-26 17:21:24.763',NULL,'411111111111',20) ON DUPLICATE KEY UPDATE `user_id`=VALUES(`user_id`)

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu',NULL,0,NULL,NULL,NULL,'2022-05-26 17:21:24.761','2022-05-26 17:21:24.761')

跳过指定关联

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	db.Omit("CreditCard").Create(&User{Name: "jinzhu"})
}

日志:

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu',NULL,0,NULL,NULL,NULL,'2022-05-26 17:25:34.453','2022-05-26 17:25:34.453')

跳过所有关联

package main

import (
	"database/sql"
	"fmt"
	"/driver/mysql"
	"/gorm"
	"/gorm/logger"
	"log"
	"os"
	"time"
)
// 省略表结构
func main() {
	// 省略连接数据库代码
	db.Omit(clause.Associations).Create(&User{Name: "jinzhu"})
}

日志:

INSERT INTO `users` (`name`,`email`,`age`,`birthday`,`member_number`,`activated_at`,`created_at`,`updated_at`) VALUES ('jinzhu',NULL,0,NULL,NULL,NULL,'2022-05-26 17:27:12.382','2022-05-26 17:27:12.382')

相关文章