gorm实现数据库连接以及创建，查询，更新，删除的测试

数据库操作(mysql，gorm实现基本的增删改查)

连接

//连接数据库
dsn := "root:password@tcp(localhost)/sql_test?charset=utf8mb4&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
//错误判断
if err != nil {
	panic(err)
}
//创建一个表表名叫UserTest，进入数据库中后名字为user_tests
db.AutoMigrate(&UserTest{})

MySQl 驱动程序提供了 一些高级配置 可以在初始化过程中使用

db, err := gorm.Open(mysql.New(mysql.Config{
  DSN: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // DSN data source name
  DefaultStringSize: 256, // string 类型字段的默认长度
  DisableDatetimePrecision: true, // 禁用 datetime 精度，MySQL 5.6 之前的数据库不支持
  DontSupportRenameIndex: true, // 重命名索引时采用删除并新建的方式，MySQL 5.7 之前的数据库和 MariaDB 不支持重命名索引
  DontSupportRenameColumn: true, // 用 `change` 重命名列，MySQL 8 之前的数据库和 MariaDB 不支持重命名列
  SkipInitializeWithVersion: false, // 根据当前 MySQL 版本自动配置
}), &gorm.Config{})

创建

type UserTest struct {
	gorm.Model
	Name     string
	Birthday time.Time
}

func main4() {
	//连接数据库
	dsn := "root:password@tcp(localhost)/sql_test?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	//错误判断
	if err != nil {
		panic(err)
	}
	//创建一个表表名叫UserTest，进入数据库中后名字为user_tests
	db.AutoMigrate(&UserTest{})
	//创建记录
	user := UserTest{Name: "zxp", Birthday: time.Now()}
	db.Create(&user)

用指定的字段创建记录

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

//创建记录
user1 := UserTest{Name: "张三", Birthday: time.Now()}
//用指定的字段创建记录，比如只插入Name和CreatedAt
db.Select("Name", "CreatedAt").Create(&user1)*/

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

//创建一个记录且一同忽略传递略去的字段值
//比如Name不会传值
user1 := UserTest{Name: "李四", Birthday: time.Now()}
db.Omit("Name").Create(&user1)

批量插入

var users = []UserTest{
		{Name: "王五"},
		{Name: "小红"},
		{Name: "小明"},
	}
db.Create(&users)

CreateInBatches

//创建记录
var users = []User{{name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}

// 数量为 100
db.CreateInBatches(users, 100)

根据Map创建

//根据map创建
	db.Model(&UserTest{}).Create(map[string]interface{}{
		"Name":     "张三",
		"Birthday": time.Now(),
	})
//根据map切片创建
	db.Model(&UserTest{}).Create([]map[string]interface{}{
		{"Name": "我", "Birthday": time.Now(), "CreatedAt": time.Now()},
		{"Name": "爱", "Birthday": time.Now(), "CreatedAt": time.Now()},
		{"Name": "你", "Birthday": time.Now(), "CreatedAt": time.Now()},
	})

查询

// 获取第一条记录（主键升序）
db.First(&user)
// SELECT * FROM users ORDER BY id LIMIT 1;

// 获取一条记录，没有指定排序字段
db.Take(&user)
// SELECT * FROM users LIMIT 1;

// 获取最后一条记录（主键降序）
db.Last(&user)
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

result := db.First(&user)
result.RowsAffected // 返回找到的记录数
result.Error        // returns error or nil

// 检查 ErrRecordNotFound 错误
errors.Is(result.Error, gorm.ErrRecordNotFound)

测试：

import (
	"fmt"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)
func main(){
    //连接数据库
	dsn := "root:zxp990926@tcp(localhost)/sql_test?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	//错误判断
	if err != nil {
		panic(err)
	}
    //实例化结构体，也就是对应要查询的表的结构体
	var b UserTest
	//查询第一条记录
	/*db.First(&b)
	fmt.Printf("%v\n", b)
	fmt.Println("==========================")
	*/

	/*// 获取一条记录，没有指定排序字段
	db.Last(&b)
	fmt.Printf("%v\n", b)
	fmt.Println("==========================")*/

	/*result := db.First(&b)
	affected := result.RowsAffected //返回找到的记录数
	fmt.Println(affected)
	fmt.Println("==========================")*/

	/*//查询指定人的部分信息
	db.Find(&b, "Name=?", "我")
	fmt.Println(b.ID)
	fmt.Println("==========================")*/

	// 检查 ErrRecordNotFound 错误
	//errors.Is(result.Error, gorm.ErrRecordNotFound)

	//用主键检索
	db.First(&b, "2")
	fmt.Println(b)
	fmt.Println("==========================")
}

注意first和last

FirstLastdb.Model()

var user User
var users []User  

// 有效，因为目标 struct 是指针
db.First(&user)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

// 有效，因为通过 `db.Model()` 指定了 model
result := map[string]interface{}{}
db.Model(&User{}).First(&result)
// SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1

// 无效
result := map[string]interface{}{}
db.Table("users").First(&result)

// 配合 Take 有效
result := map[string]interface{}{}
db.Table("users").Take(&result)

// 未指定主键，会根据第一个字段排序(即：`Code`)
type Language struct {
  Code string
  Name string
}
db.First(&Language{})
// SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1

查询所有对象

//检索所有对象
var users []UserTest
result := db.Find(&users)
fmt.Println(users)
affected := result.RowsAffected
fmt.Println(affected) //7

where查询

// 获取第一条匹配的记录
db.Where("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;

// 获取全部匹配的记录
db.Where("name <> ?", "jinzhu").Find(&users)
// SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

测试

package main

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

func main() {
	dsn := "root:zxp990926@tcp(localhost)/sql_test?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	//错误判断
	if err != nil {
		panic(err)
	}
	//var b UserTest

	/*//获取第一条匹配的记录
	db.Where("name = ?", "我").Find(&b)
	fmt.Println(b)
	fmt.Println("=========================================")*/

	获取全部匹配的记录
	//var users []UserTest
	//db.Where("Name <> ?", "爱").Find(&users)
	//fmt.Println(users)
	//fmt.Println("=========================================")

	/*//根据名字获得对应的记录
	var users []UserTest
	db.Where("Name IN ?", []string{"我", "爱"}).Find(&users)
	fmt.Println(users)*/

	/*var users []UserTest
	//有数据库可知能查到小明和小红
	db.Where("Name LIKE ?", "%小%").Find(&users)
	fmt.Println(users)*/

	/*//查询名字叫小红并且生日大于等于0000-00-00 00:00:00.000
	var users []UserTest
	db.Where("Name = ? AND Birthday >= ?", "小红", "0000-00-00 00:00:00.000").Find(&users)
	fmt.Println(users)*/

	/*//时间比
	var users []UserTest
	lastweek := time.Now()
	db.Where("updated_at > ?", lastweek).Find(&users)
	fmt.Println(users)*/

}

Struct & Map 条件的where查询

// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// 主键切片条件
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);

db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu";

内联条件

// 根据主键获取记录，如果是非整型主键
db.First(&user, "id = ?", "string_primary_key")
// SELECT * FROM users WHERE id = 'string_primary_key';

// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
// SELECT * FROM users WHERE name = "jinzhu";

db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;

// Struct
db.Find(&users, User{Age: 20})
// SELECT * FROM users WHERE age = 20;

// Map
db.Find(&users, map[string]interface{}{"age": 20})
// SELECT * FROM users WHERE age = 20;

Not条件

db.Not("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;
测试：
//Not条件
//实例化结构体
var user1 UserTest
db.Not("Name = ?", "王五").First(&user1)
fmt.Println(user1)
查询结果为第二行的小红，如果填写的名字不是王五则查询的结果为王五


// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");
测试：
var users []UserTest
db.Not(map[string]interface{}{"Name": []string{"王五", "爱"}}).Find(&users)
fmt.Printf("%v\n", users)
查询结果为除去王五和爱的所有人


// 不在主键切片中的记录
db.Not([]int64{1,2,3}).First(&user)
// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
测试
var user1 UserTest
db.Not([]int64{1, 2, 3}).First(&user1)
fmt.Println(user1)
查询结果为id = 4的张三

or条件

db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);

测试：

var users []UserTest
db.Where("Name = ?", "张三").Or("Name = ?", "爱").Find(&users)
fmt.Println(users)
//查询结果为张三和爱的记录

选择特定字段

db.Select("name", "age").Find(&users)
// SELECT name, age FROM users;

db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM users;

db.Table("users").Select("COALESCE(age,?)", 42).Rows()
// SELECT COALESCE(age,'42') FROM users;

测试：

var users1 []UserInfo
db.Select("Name", "Hobby").Find(&users1)
fmt.Println(&users1)
查询结果为：[{0 张三  学习} {0 李四  玩} {0 zxp  hahaha}]

Order

db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// 多个 order
db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

db.Clauses(clause.OrderBy{
  Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},
}).Find(&User{})
// SELECT * FROM users ORDER BY FIELD(id,1,2,3)

测试：

var users1 []UserInfo
db.Order("ID desc,Name ").Find(&users1)//升序用asc,降序用desc
fmt.Println(users1)
//根据ID进行降序排列
输出结果为：[{3 zxp 男 hahaha} {2 李四 男 玩} {1 张三 男 学习}]

var users1 []UserInfo
db.Order("ID desc").Order("Hobby").Find(&users1)
fmt.Println(users1)
输出结果为：[{3 zxp 男 hahaha} {2 李四 男 玩} {1 张三 男 学习}]

//自定义排序
//FIELD()函数是将参数1的字段对后续参数进行比较，并返回1、2、3等等，如果遇到null或者没有在结果集上存在的数据，则返回0，然后根据升序进行排序。
	var users1 []UserInfo
	db.Clauses(clause.OrderBy{
		Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 3, 2}}, WithoutParentheses: true},
	}).Find(&users1)
	fmt.Println(users1)
输出结果为：[{1 张三 男 学习} {3 zxp 男 hahaha} {2 李四 男 玩}]

Limit &Offset

LimitOffset

db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

// 通过 -1 消除 Limit 条件
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
// SELECT * FROM users LIMIT 10; (users1)
// SELECT * FROM users; (users2)

db.Offset(3).Find(&users)
// SELECT * FROM users OFFSET 3;

db.Limit(10).Offset(5).Find(&users)
// SELECT * FROM users OFFSET 5 LIMIT 10;

// 通过 -1 消除 Offset 条件
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

测试：

result := db.Limit(2).Find(&users1)
affected := result.RowsAffected
fmt.Println(users1)
fmt.Println(affected)
输出结果为：[{1 张三 男 学习} {2 李四 男 玩}]
		  2

// 通过 -1 消除 Limit 条件
result := db.Limit(2).Find(&users1).Limit(-1).Find(&users1)
affected := result.RowsAffected
fmt.Println(users1)
fmt.Println(affected)
输出结果为：[{1 张三 男 学习} {2 李四 男 玩} {3 zxp 男 hahaha}]
3

Distinct(去重复)

db.Distinct("name", "age").Order("name, age desc").Find(&results)
 //SELECT DISTINCT `name`,`age` FROM `results` ORDER BY Name,Age desc

joins(多表连接)

type result struct {
  Name  string
  Email string
}

db.Model(&User{}).Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&result{})
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

rows, err := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Rows()
for rows.Next() {
  ...
}

db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

// 带参数的多表连接
db.Joins("JOIN emails ON emails.user_id = users.id AND emails.email = ?", "jinzhu@example.org").Joins("JOIN credit_cards ON credit_

Scan

import (
	"fmt"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

type Result struct {
	Name string
	Age  int
}

func main9() {
	//连接数据库
	dsn := "root:zxp990926@tcp(localhost)/sql_test?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	//错误判断
	if err != nil {
		panic(err)
	}
	//scan
	var rets Result
	db.Table("user1").Select("Name", "Age").Where("Name = ?", "stu4").Scan(&rets)
	fmt.Println(rets)//{stu4 19}
	fmt.Println("======================")

	var rets1 Result
	db.Raw("select Name,Age from user1 where Name = ?", "stu3").Scan(&rets1)
	fmt.Println(rets1)//{stu3 19}   

}

高级查询(略)

type User1 struct {
	ID        uint `gorm:"primaryKey"`
	Name      string
	Age       int
	CreatedAt time.Time
	UpdateAt  time.Time
}
type Result struct {
	Name string
	Age  int
}

var rest2 []Result
db.Model(&User1{}).Select("Name", "Age").Where("Age > ?", 18).Limit(3).Find(&rest2)
fmt.Println(rest2)
查询结果为：[{stu2 20} {stu3 19} {stu4 19}]

更新

保存所有字段

Save

//实例化一个表的结构体
var user User1
db.First(&user)
user.Age = 30
db.Save(&user)//age = 30

更新单个列

UpdateErrMissingWhereClauseModel

//更新单个列
db.Model(&User1{}).Where("Age = ?", 19).Update("Name", "love you")

更新多列

Updatesstructmap[string]interface{}struct

//更新多列
db.Model(&User1{}).Where("Name = ?", "love you").Updates(map[string]interface{}{
	"Name": "zxp", "Age": 20,
})
db.Table("user1").Where("id IN ?", []int{3, 4, 6}).Updates(map[string]interface{}{
		"Name": "zxp",
})

Selectmap

更新选定字段

//更新选定字段(只更新了name)
db.Model(&User1{}).Select("Name").Where("Name = ?", "zxp").Updates(map[string]interface{}{
		"Name": "love you",
		"Age":  100,
})

//(只更新了未选中的age)
db.Model(&User1{}).Omit("Name").Where("Name = ?", "love you").Updates(User1{
	Name: "zcp",
	Age:  20,
})

阻止全局更新

db.Model(&User{}).Update("name", "jinzhu").Error // gorm.ErrMissingWhereClause
//实现多更新
//age的年龄都将是18
db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User1{}).Update("Age", 18)

db.Model(&User{}).Where("1 = 1").Update("name", "zxp")
// UPDATE users SET `name` = "jinzhu" WHERE 1=1

更新的记录数

result := db.Model(&User1{}).Select("Name", "Age").Where("Name = ?", "zxp").Updates(map[string]interface{}{
		"Name": "love you",
		"Age":  20,
	})
fmt.Println(result.RowsAffected)

使用sql表达式更新

db.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))
// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = 3 AND quantity > 1;

根据子查询进行更新

db.Model(&user).Update("company_name", db.Model(&Company{}).Select("name").Where("companies.id = users.company_id"))
// UPDATE "users" SET "company_name" = (SELECT name FROM companies WHERE companies.id = users.company_id);

db.Table("users as u").Where("name = ?", "jinzhu").Update("company_name", db.Table("companies as c").Select("name").Where("c.id = u.company_id"))

db.Table("users as u").Where("name = ?", "jinzhu").Updates(map[string]interface{}{}{"company_name": db.Table("companies as c").Select("name").Where

返回修改行的数据

// 返回所有列
var users []User
DB.Model(&users).Clauses(clause.Returning{}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING *
// users => []User{{ID: 1, Name: "jinzhu", Role: "admin", Salary: 100}, {ID: 2, Name: "jinzhu.2", Role: "admin", Salary: 1000}}


// 返回指定的列
DB.Model(&users).Clauses(clause.Returning{Columns: []clause.Column{{Name: "name"}, {Name: "salary"}}}).Where("role = ?", "admin").Update("salary", gorm.Expr("salary * ?", 2))
// UPDATE `users` SET `salary`=salary * 2,`updated_at`="2021-10-28 17:37:23.19" WHERE role = "admin" RETURNING `name`, `salary`
// users => []User{{ID: 0, Name: "jinzhu", Role: "", Salary: 100}, {ID: 0, Name: "jinzhu.2", Role: "", Salary: 1000}}

删除

删除一条记录

// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;

// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";

根据主键删除

db.Delete(&User{}, 10)
// DELETE FROM users WHERE id = 10;

db.Delete(&User{}, "10")
// DELETE FROM users WHERE id = 10;

db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);

批量删除

db.Where("email LIKE ?", "%jinzhu%").Delete(Email{})
// DELETE from emails where email LIKE "%jinzhu%";

db.Delete(Email{}, "email LIKE ?", "%jinzhu%")
// DELETE from emails where email LIKE "%jinzhu%";

软删除

gorm.deletedatgorm.Model

DeleteDeletedAt

// user 的 ID 是 `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 批量删除
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// 在查询时会忽略被软删除的记录
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

gorm.Model

type User struct {
  ID      int
  Deleted gorm.DeletedAt
  Name    string
}

永久删除

db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;

u%")
// DELETE from emails where email LIKE “%jinzhu%”;

##### 软删除

如果您的模型包含了一个 `gorm.deletedat` 字段（`gorm.Model` 已经包含了该字段)，它将自动获得软删除的能力！

拥有软删除能力的模型调用 `Delete` 时，记录不会被数据库。但 GORM 会将 `DeletedAt` 置为当前时间， 并且你不能再通过普通的查询方法找到该记录。

```go
// user 的 ID 是 `111`
db.Delete(&user)
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 批量删除
db.Where("age = ?", 20).Delete(&User{})
// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// 在查询时会忽略被软删除的记录
db.Where("age = 20").Find(&user)
// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

gorm.Model

type User struct {
  ID      int
  Deleted gorm.DeletedAt
  Name    string
}

永久删除

db.Unscoped().Delete(&order)
// DELETE FROM orders WHERE id=10;