GORM数据库操作实战-CSDN博客

本文链接：https://blog.csdn.net/umgsai/article/details/123771670

本文介绍使用GORM进行数据库操作的详细步骤，包括初始化项目、设置代理、定义数据模型、执行CRUD操作及复杂查询等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

初始化go mod项目

[  9:56PM ]  [ shangyidong@shangyidongdeiMac:~/go/src/awesomeProject ]
$ go mod init gitee/shang/awesomeProject
go: creating new go.mod: module gitee/shang/awesomeProject
go: to add module requirements and sums:
        go mod tidy

设置GOPROXY

GOPROXY=Goproxy.cn

type TestUser struct {
   ID           uint           `gorm:"primaryKey;comment:主键"`
   Name         string         `gorm:"index;not null;default:'';comment:姓名"`
   Email        string        `gorm:"not null;default:'';comment:邮箱"`
   Age          uint8          `gorm:"not null;default:0;comment:年龄"`
   Birthday     time.Time     `gorm:"not null;default:1970-01-01 00:00:00;comment:年龄"`
   MemberNumber sql.NullString `gorm:"not null;default:'';comment:xx"`
   ActivatedAt  sql.NullTime   `gorm:"column:activated_at;not null;default:1970-01-01 00:00:00;comment:激活时间"`
   CreatedAt    time.Time      `gorm:"index;default:null;comment:创建时间" json:"createTime"`
   UpdatedAt    time.Time      `gorm:"index;default:null;comment:更新时间" json:"updateTime"`
   DeletedAt    gorm.DeletedAt `gorm:"index"`
}

以上的结构模型对应的建表语句如下

CREATE TABLE `t_test_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(171) NOT NULL DEFAULT '' COMMENT '姓名',
  `email` varchar(171) NOT NULL DEFAULT '' COMMENT '邮箱',
  `age` tinyint(3) unsigned NOT NULL DEFAULT 0 COMMENT '年龄',
  `birthday` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '年龄',
  `member_number` varchar(171) NOT NULL COMMENT 'xx',
  `activated_at` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '激活时间',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_t_test_user_deleted_at` (`deleted_at`),
  KEY `idx_t_test_user_name` (`name`),
  KEY `idx_t_test_user_created_at` (`created_at`),
  KEY `idx_t_test_user_updated_at` (`updated_at`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4


func main() {
	//dsn := "root:@tcp(127.0.0.1:3306)/gorm_class?charset=utf8mb4&parseTime=True&loc=Local"
	//db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	db, err := gorm.Open(mysql.New(mysql.Config{
		DSN:                       "root:@tcp(127.0.0.1:3306)/gorm_class?charset=utf8mb4&parseTime=True&loc=Local", // DSN data source name
		DefaultStringSize:         171,                                                                             // string 类型字段的默认长度
		DisableDatetimePrecision:  true,                                                                            // 禁用 datetime 精度，MySQL 5.6 之前的数据库不支持
		DontSupportRenameIndex:    true,                                                                            // 重命名索引时采用删除并新建的方式，MySQL 5.7 之前的数据库和 MariaDB 不支持重命名索引
		DontSupportRenameColumn:   true,                                                                            // 用 `change` 重命名列，MySQL 8 之前的数据库和 MariaDB 不支持重命名列
		SkipInitializeWithVersion: false,                                                                           // 根据当前 MySQL 版本自动配置
	}), &gorm.Config{
		SkipDefaultTransaction: true, //跳过默认事务
		NamingStrategy: schema.NamingStrategy{
			TablePrefix:   "t_", // table name prefix, table for `User` would be `t_users`
			SingularTable: true, // use singular table name, table for `User` would be `user` with this option enabled
			//NoLowerCase:   true,                              // skip the snake_casing of names
			//NameReplacer:  strings.NewReplacer("CID", "Cid"), // use name replacer to change struct/field name before convert it to db name
		},
		NowFunc: func() time.Time {
			return time.Now().Local()
		},
		DisableForeignKeyConstraintWhenMigrating: true, //在 AutoMigrate 或 CreateTable 时，GORM 会自动创建外键约束，若要禁用该特性，可将其设置为 true, 不要物理外键，使用逻辑外键
		Logger:                                   logger.Default.LogMode(logger.Info),
	})
	fmt.Println(db, err)

	type User struct {
		Name string
	}

	type TestUser struct {
		ID           uint           `gorm:"primaryKey;comment:主键"`
		Name         string         `gorm:"index;not null;default:'';comment:姓名"`
		Email        string         `gorm:"not null;default:'';comment:邮箱"`
		Age          uint8          `gorm:"not null;default:0;comment:年龄"`
		Birthday     time.Time      `gorm:"not null;default:1970-01-01 00:00:00;comment:年龄"`
		MemberNumber sql.NullString `gorm:"not null;default:'';comment:xx"`
		ActivatedAt  sql.NullTime   `gorm:"column:activated_at;not null;default:1970-01-01 00:00:00;comment:激活时间"`
		CreatedAt    time.Time      `gorm:"index;default:null;comment:创建时间" json:"createTime"`
		UpdatedAt    time.Time      `gorm:"index;default:null;comment:更新时间" json:"updateTime"`
		DeletedAt    gorm.DeletedAt `gorm:"index"`
	}
	// https://gorm.io/zh_CN/docs/migration.html
	// 自动建表
	// err = db.AutoMigrate(&User{})
	/*
		M := db.Migrator()
		// 建表
		err = M.CreateTable(&User{})
		hasTable := M.HasTable(&User{})
		fmt.Println(hasTable)
		// 判断表是否存在
		hasTable = M.HasTable("t_user")
		fmt.Println(hasTable)
		// 删除表
		err = M.DropTable(&User{})
		hasTable = M.HasTable(&User{})
		fmt.Println(hasTable)

		if !hasTable {
			err = M.CreateTable(&User{})
			err := M.RenameTable(&User{}, "t_user_0")
			fmt.Println(err)
		}
	*/

	sqlDB, err := db.DB()
	sqlDB.SetMaxIdleConns(10)           //连接池最大空闲连接数
	sqlDB.SetMaxOpenConns(100)          //连接池最大连接数
	sqlDB.SetConnMaxLifetime(time.Hour) //连接池中链接最大可复用时间

	fmt.Println(sqlDB)

	GLOBAL_DB = db

	//TestCreateUser()
	GLOBAL_DB.AutoMigrate(&TestUser{})
	// 插入单条数据
	create := GLOBAL_DB.Create(&TestUser{
		Name:  "admin",
		Email: "admin@126.com",
   		Birthday:    time.Now(),
	    ActivatedAt: sql.NullTime{Time: time.Time{}},
		CreatedAt:   time.Now(),
		UpdatedAt:   time.Now(),
	})
	fmt.Println(create)
	// 批量插入数据
	create = GLOBAL_DB.Create(&[]TestUser{
		{
			Name:        "admin1",
			Email:       "admin1@126.com",
			Birthday:    time.Now(),
			ActivatedAt: sql.NullTime{Time: time.Time{}},
			CreatedAt:   time.Now(),
			UpdatedAt:   time.Now(),
		},
		{
			Name:        "admin2",
			Email:       "admin2@126.com",
			Birthday:    time.Now(),
			ActivatedAt: sql.NullTime{Time: time.Time{}},
			CreatedAt:   time.Now(),
			UpdatedAt:   time.Now(),
		},
	})
	fmt.Println(create)

	// 用Map来接收查询的数据
	var result = make(map[string]interface{})
	//  SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Model(&TestUser{}).First(&result)
	//fmt.Println(result)

	jsonResult, err := json.Marshal(result)
	if err != nil {
		fmt.Println("JSON ERR:", err)
	} else {
		// {"activated_at":"1970-01-01T00:00:00+08:00","age":0,"birthday":"1970-01-01T00:00:00+08:00","created_at":null,"deleted_at":null,"email":"admin@126.com","id":1,"member_number":"","name":"admin","updated_at":null}
		fmt.Println(string(jsonResult))
	}

	// 用TestUser来接收查询的数据
	var testUser TestUser
	// 对应的SQL：SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Model(&TestUser{}).First(&testUser)
	jsonResult, err = json.Marshal(testUser)
	if err != nil {
		fmt.Println("JSON ERR:", err)
	} else {
		// {"ID":1,"Name":"admin","Email":"admin@126.com","Age":0,"Birthday":"1970-01-01T00:00:00+08:00","MemberNumber":{"String":"","Valid":true},"ActivatedAt":{"Time":"1970-01-01T00:00:00+08:00","Valid":true},"createTime":"0001-01-01T00:00:00Z","updateTime":"0001-01-01T00:00:00Z","DeletedAt":null}
		fmt.Println(string(jsonResult))
	}

	// 获取一条记录，没有指定排序字段
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL AND `t_test_user`.`id` = 1 LIMIT 1
	GLOBAL_DB.Model(&TestUser{}).Take(&testUser)
	fmt.Println(testUser)

	// 获取最后一条记录（主键降序）
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL AND `t_test_user`.`id` = 1 ORDER BY `t_test_user`.`id` DESC LIMIT 1
	GLOBAL_DB.Model(&TestUser{}).Last(&testUser)
	fmt.Println(testUser)

	queryResult := GLOBAL_DB.Model(&TestUser{}).First(&testUser)
	// 返回找到的记录数 1
	fmt.Println(queryResult.RowsAffected)
	// returns error or nil
	fmt.Println(queryResult.Error)

	// 检查 ErrRecordNotFound 错误
	is := errors.Is(queryResult.Error, gorm.ErrRecordNotFound)
	// false
	fmt.Println(is)

	var queryUser TestUser
	// 主键检索
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`id` = 10 AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.First(&queryUser, 10)
	fmt.Println(queryUser)

	queryUser = TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`id` = '10' AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.First(&queryUser, "10")
	fmt.Println(queryUser)

	var queryUsers []TestUser
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`id` IN (1,2,3) AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Find(&queryUsers, []int{1, 2, 3})
	fmt.Println(queryUsers)

	queryUser = TestUser{}
	// SELECT * FROM `t_test_user` WHERE name = 'admin' AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Where("name = ?", "admin").First(&queryUser)
	fmt.Println(queryUser)

	// 使用Struct作为查询条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`name` = 'admin' AND `t_test_user`.`age` = 20 AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Where(&TestUser{Name: "admin", Age: 20}).First(&queryUsers)
	fmt.Println(queryUsers)

	// 使用Map作为查询条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `age` = 20 AND `name` = 'admin' AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Where(map[string]interface{}{"name": "admin", "age": 20}).Find(&queryUsers)
	fmt.Println(queryUsers)

	// 主键切片条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`id` IN (20,21,22) AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Where([]int64{20, 21, 22}).Find(&queryUsers)
	fmt.Println(queryUsers)

	// 当使用结构作为条件查询时，GORM 只会查询非零值字段。这意味着如果您的字段值为 0、''、false 或其他 零值，该字段不会被用于构建查询条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`name` = 'admin' AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Where(&TestUser{Name: "admin", Age: 0}).First(&queryUsers)
	fmt.Println(queryUsers)

	// 如果想要包含零值查询条件，可以使用 map，其会包含所有 key-value 的查询条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `age` = 0 AND `name` = 'admin' AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Where(map[string]interface{}{"name": "admin", "age": 0}).Find(&queryUsers)
	fmt.Println(queryUsers)

	// 查询条件也可以被内联到 First 和 Find 之类的方法中，其用法类似于 Where。
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE (name <> 'admin' AND age > 20) AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Find(&queryUsers, "name <> ? AND age > ?", "admin", 20)
	fmt.Println(queryUsers)

	// Not 条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE NOT name = 'admin1' AND `t_test_user`.`deleted_at` IS NULL ORDER BY `t_test_user`.`id` LIMIT 1
	GLOBAL_DB.Not("name = ?", "admin1").First(&queryUsers)
	fmt.Println(queryUsers)

	// or条件
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE (name = 'admin1' OR age = 20) AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Where("name = ?", "admin1").Or("age = ?", 20).Find(&queryUsers)
	fmt.Println(queryUsers)

	// 查询指定字段
	queryUsers = []TestUser{}
	// SELECT `name`,`age` FROM `t_test_user` WHERE id = 20 AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Select("name", "age").Where("id = ?", 20).Find(&queryUsers)
	fmt.Println(queryUsers)

	// 排序
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL ORDER BY id desc, name,`t_test_user`.`id` DESC LIMIT 1
	GLOBAL_DB.Order("id desc, name").Last(&queryUsers)
	fmt.Println(queryUsers)

	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL ORDER BY ID desc,name
	GLOBAL_DB.Order("ID desc").Order("name").Find(&queryUsers)
	fmt.Println(queryUsers)

	// Limit & Offset
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL LIMIT 3
	GLOBAL_DB.Limit(3).Find(&queryUsers)
	fmt.Println(queryUsers)

	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL ORDER BY ID desc LIMIT 10 OFFSET 5
	GLOBAL_DB.Order("ID desc").Limit(10).Offset(5).Find(&queryUsers)
	fmt.Println(queryUsers)

	// Group By & Having
	// https://gorm.io/zh_CN/docs/query.html#Group-By-amp-Having
	type GroupResult struct {
		Age   int
		Total int
	}
	var groupResult = []GroupResult{}
	// SELECT age, count(age) as total FROM `t_test_user` WHERE name LIKE '%admin%' AND `t_test_user`.`deleted_at` IS NULL GROUP BY `age`
	GLOBAL_DB.Model(&TestUser{}).Select("age, count(age) as total").Where("name LIKE ?", "%admin%").Group("age").Find(&groupResult)
	// [{0 156} {20 2} {22 2} {23 4} {25 1} {26 1} {27 1} {28 1} {32 2} {33 1} {34 1} {43 1}]
	fmt.Println(groupResult)

	// Distinct
	var distinctResult = []string{}
	// SELECT DISTINCT `name` FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Model(&TestUser{}).Distinct("name").Find(&distinctResult)
	// [admin admin1 admin2 admin3]
	fmt.Println(distinctResult)

	type ScanResult struct {
		Name string
		Age  int
	}
	var scanResult []ScanResult
	// SELECT name,age FROM `t_test_user` WHERE name = 'admin1'
	GLOBAL_DB.Table("t_test_user").Select("name", "age").Where("name = ?", "admin1").Scan(&scanResult)
	fmt.Println(scanResult)

	// Raw SQL 原生SQL查询
	var rowResult []ScanResult
	// SELECT name, age FROM t_test_user WHERE name = 'admin2'
	db.Raw("SELECT name, age FROM t_test_user WHERE name = ?", "admin2").Scan(&rowResult)
	fmt.Println(rowResult)


	// 智能选择字段查询
	type APIUser struct {
		ID   uint
		Name string
	}
	var apiUser APIUser
	// 查询时会自动选择 `id`, `name` 字段
	//  SELECT `t_test_user`.`id`,`t_test_user`.`name` FROM `t_test_user` WHERE `t_test_user`.`deleted_at` IS NULL LIMIT 10
	GLOBAL_DB.Model(&TestUser{}).Limit(10).Find(&apiUser)
	fmt.Println(rowResult)

	// Count 用于获取匹配的记录数
	var count int64
	//  SELECT count(*) FROM `t_test_user` WHERE name = 'admin' AND `t_test_user`.`deleted_at` IS NULL
	GLOBAL_DB.Model(&TestUser{}).Where("name = ?", "admin").Count(&count)
	fmt.Println(count)

	// 命名参数 https://gorm.io/zh_CN/docs/sql_builder.html#%E5%91%BD%E5%90%8D%E5%8F%82%E6%95%B0
	queryUsers = []TestUser{}
	// SELECT * FROM `t_test_user` WHERE (name = 'admin' and age = '50') AND `t_test_user`.`deleted_at` IS NULL
	db.Where("name = @name and age = @age", sql.Named("name", "admin"), sql.Named("age", "50")).Find(&queryUsers)
	fmt.Println(queryUsers)

	var unfollowerIdList []uint
	// 取100个未关注的ID
	err = engine.Table("t_user").Select("id").Not("id in (?)", followIdList).Limit(100).Pluck("id", &unfollowerIdList).GetError()
}

Gorm入门-创建&查询