gorm定制开发实现记录查询功能
目录
实验准备
定制开发建立数据库连接
import ( "fmt" _ "github.com/go-sql-driver/mysql" "gorm.io/driver/mysql" "gorm.io/gorm" "log")var db *gorm.DBfunc OpenDB() { dsn := "root:adss123@tcp(127.0.0.1:3306)/go_db?charset=utf8mb4&parseTime=True&loc=Local" res, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) db = res if err != nil { log.Fatal(err) } fmt.Printf("成功:%v\", db)}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
建立模型
type TestTb2 struct { Username string Password string TestTb2User1ID uint gorm.Model}
- 1
- 2
- 3
- 4
- 5
- 6
定制开发数据表预存数据
定制开发基本查询应用
定制开发查询单个目标
GORM 提供了 First、Take、Last 方法,定制开发以便从数据库中检索单个对象。定制开发当查询数据库时它添加了 LIMIT 1 条件,定制开发且没有找到记录时,它会返回 ErrRecordNotFound 错误
// 定制开发获取第一条记录(主键升序)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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
First 和 Last 定制开发会根据主键排序,定制开发分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.Model() 指定 模型时,该方法才有效。 此外,如果相关 模型没有定义主键,那么将按 模型的第一个字段进行排序。
测试案例如下:
struct指针的搜索函数:(只运行First方法)
func QueryOneRow(Any any) { OpenDB() first := db.First(Any) if first.Error != nil { fmt.Printf("first查询失败,err:%v\", first.Error) } else { fmt.Printf("first查询成功,结果为:%v\", Any) } //last := db.Last(Any) //if last.Error != nil { // fmt.Printf("last查询失败,err:%v\", last.Error) //} else { // fmt.Printf("last查询成功,结果为:%v\", Any) //} //take := db.Take(Any) //if take.Error != nil { // fmt.Printf("take查询失败,err:%v\", take.Error) //} else { // fmt.Printf("take查询成功,结果为:%v\", Any) //}}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
db.Model()指定的搜索(只运行Last方法)
result := map[string]interface{}{} last := db.Model(&TestTb2{}).Last(&result) if last.Error != nil { fmt.Printf("last查询失败,err:%v\", last.Error) } else { fmt.Printf("last查询成功,结果为:%v\", result) }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
未解决问题
当一个函数中利用结构体指针为目标同时调用Last与First时,查询结果相同,为先执行的方法结果。如:
func QueryOneRow(Any any) { OpenDB() first := db.First(Any) if first.Error != nil { fmt.Printf("first查询失败,err:%v\", first.Error) } else { fmt.Printf("first查询成功,结果为:%v\", Any) } last := db.Last(Any) if last.Error != nil { fmt.Printf("last查询失败,err:%v\", last.Error) } else { fmt.Printf("last查询成功,结果为:%v\", Any) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
但db.Model()指定的模型为目标搜索时,此情况不会出现。如:
func QueryOneRow(Any any) { OpenDB() result := map[string]interface{}{} first := db.Model(&TestTb2{}).First(&result) if first.Error != nil { fmt.Printf("first查询失败,err:%v\", first.Error) } else { fmt.Printf("first查询成功,结果为:%v\", result) } last := db.Model(&TestTb2{}).Last(&result) if last.Error != nil { fmt.Printf("last查询失败,err:%v\", last.Error) } else { fmt.Printf("last查询成功,结果为:%v\", result) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
通过主键查询
如果主键是数字类型,您可以使用 内联条件 来检索对象。
db.First(&user, 10)// SELECT * FROM users WHERE id = 10;db.First(&user, "10")// SELECT * FROM users WHERE id = 10;db.Find(&users, []int{1,2,3})// SELECT * FROM users WHERE id IN (1,2,3);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
如果主键是字符串(例如像 uuid),查询将被写成这样:
db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
- 1
- 2
实验案例如下:
func QueryOneRowById(Any any, id int) { OpenDB() take := db.Take(Any, id) if take.Error != nil { fmt.Printf("take查询失败,err:%v\", take.Error) } else { fmt.Printf("take查询成功,结果为:%v\", Any) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
特别地,当gorm.Model结构体中DeletedAt不为空时,搜索结果为:
查询全部记录
利用Find方法可查询全部记录,
// Get all recordsresult := db.Find(&users)// SELECT * FROM users;result.RowsAffected // returns found records count, equals `len(users)`result.Error // returns error
- 1
- 2
- 3
- 4
- 5
- 6
实验案例如下
func QueryManyRows(Any any) { OpenDB() find := db.Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
条件
String 条件
命令类似sql语句
// Get first matched recorddb.Where("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;// Get all matched recordsdb.Where("name <> ?", "jinzhu").Find(&users)// SELECT * FROM users WHERE name <> 'jinzhu';// INdb.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');// LIKEdb.Where("name LIKE ?", "%jin%").Find(&users)// SELECT * FROM users WHERE name LIKE '%jin%';// ANDdb.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;// Timedb.Where("updated_at > ?", lastWeek).Find(&users)// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';// BETWEENdb.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';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
语句规则和含义与 sql语句类似,不做过多解释。
实验案例如下:
=
func QueryWithCondition(Any any) { OpenDB() find := db.Where("username=?", "ylj").Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
<>
func QueryWithCondition(Any any) { OpenDB() find := db.Where("username<>?", "ylj").Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
IN
func QueryWithCondition(Any any) { OpenDB() find := db.Where("username IN ?", []string{"1", "2"}).Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
LIKE
func QueryWithCondition(Any any) { OpenDB() find := db.Where("username LIKE ?", "%l%").Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
后续条件效果类似,不再做出展示。
Struct & Map 条件
// Structdb.Where(&User{Name: "jinzhu", Age: 20}).First(&user)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;// Mapdb.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;// Slice of primary keysdb.Where([]int64{20, 21, 22}).Find(&users)// SELECT * FROM users WHERE id IN (20, 21, 22);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
特别的,当使用结构体作为条件查询时,gorm只会查询非零值字段。如果字段的值为0,“”,false或其他零值,该字段不会被用于构建查询条件,例如:
db.Where(&User{Name: "jinzhu", Age: 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu";
- 1
- 2
实验案例如下:
func QueryWithCondition(Any any) { OpenDB() condition := TestTb2{Username: "1", Password: ""} find := db.Where(condition).Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查询到所有密码为"1"与为空的记录,证明Password字段没起到任何约束效果。
如果想要包含零值查询条件,可以使用map, 例如:
db.Where(map[string]interface{}{"Name": "jinzhu", "Age": 0}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 0;
- 1
- 2
实验案例如下:
func QueryWithCondition(Any any) { OpenDB() find := db.Where(map[string]interface{}{"Username": "1", "Password": nil}).Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
平时操作时发现,在不定义默认值时,字符型字段在数据库默认值为nil,并非"",所以在建表时,设置默认值,避免后续出现bug
内联条件
查询条件也可以被内联到First与Find之类的方法中,用法类似Where
// Get by primary key if it were a non-integer typedb.First(&user, "id = ?", "string_primary_key")// SELECT * FROM users WHERE id = 'string_primary_key';// Plain SQLdb.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;// Structdb.Find(&users, User{Age: 20})// SELECT * FROM users WHERE age = 20;// Mapdb.Find(&users, map[string]interface{}{"age": 20})// SELECT * FROM users WHERE age = 20;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
上文通过主键查询就利用内联条件方法。结果类似,不再做实验证明。
NOT条件
构建NOT条件,用法与where类似
db.Not("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;// Not Indb.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");// Structdb.Not(User{Name: "jinzhu", Age: 18}).First(&user)// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;// Not In slice of primary keysdb.Not([]int64{1,2,3}).First(&user)// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
相当于where取否,不做过多证明。
Or 条件
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';// Structdb.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);// Mapdb.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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
选择特定字段
Select 运行指定从数据库检索哪些字段,默认检索所有
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
** 实验案例如下**
func QueryWithCondition(Any any) { OpenDB() condition := TestTb2{Username: "1", Password: "1"} find := db.Where(condition).Select("username", "password").Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
上述结果,只有被选中的字段有值,其他均为默认值。
Order
指定从数据库检索记录时的排序方式
db.Order("age desc, name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;// Multiple ordersdb.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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
实验案例如下
其中着重测试存在两个Order时,执行排列的顺序
func QueryWithCondition(Any any) { OpenDB() find := db.Order("username,id desc").Find(Any) if find.Error != nil { fmt.Printf("find查询失败,err:%v\", find.Error) } else { fmt.Printf("find查询成功,共查询到%v条\", find.RowsAffected) }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
结果可以看出,先执行考前的Order,即username的顺序,当username相同时,执行靠后的Order,即Id的倒序。
Limit & Offset
Limit 指定获取记录的最大数量,Offset指定在开始返回记录之前要跳过的记录数量。
db.Limit(3).Find(&users)// SELECT * FROM users LIMIT 3;// Cancel limit condition with -1db.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;// Cancel offset condition with -1db.Offset(10).Find(&users1).Offset(-1).Find(&users2)// SELECT * FROM users OFFSET 10; (users1)// SELECT * FROM users; (users2)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
在实现分页器的时候再着重实验
Group By & Having
type result struct { Date time.Time Total int}db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()defer rows.Close()for rows.Next() { ...}rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()defer rows.Close()for rows.Next() { ...}type Result struct { Date time.Time Total int64}db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
Scan
将结果扫描到一个结构中,工作方式与我们使用Find的方式类似。
type Result struct { Name string Age int}var result Resultdb.Table("users").Select("name", "age").Where("name = ?", "Antonio").Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
gorm 还支持很多高级查询,本人作为初学者,再后续应用到时再经行学习。
子语句查询
子查询
子查询可以在查询中,GORM 允许在使用 *gorm.DB 对象作为参数时生成子查询
db.Where("amount > (?)", db.Table("orders").Select("AVG(amount)")).Find(&orders)// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");subQuery := db.Select("AVG(age)").Where("name LIKE ?", "name%").Table("users")db.Select("AVG(age) as avgage").Group("name").Having("AVG(age) > (?)", subQuery).Find(&results)// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")
- 1
- 2
- 3
- 4
- 5
- 6
- 7
实验案例如下
表结构
type Test struct { gorm.Model InfoID int Info Info}type Info struct { gorm.Model Age int Sex string UserID int User User}type User struct { gorm.Model UserName string CompanyID string Company Company}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
func QueryBelongsto() { OpenDB() test := &Test{} info := &Info{} user := &User{} db.Where("info_id=(?)", db.Select("id").Where("user_id=(?)", db.Select("id").Where("user_name=?", "y").Find(user)).First(info)).Preload("Info.User").Preload("Info").Find(test) //db.Select("id").Where("user_name=?", "y").First(user) //db.Select("id").Where("user_id=?", user.ID).Find(info) //db.Where("info_id=?", info.ID).Preload("Info").Preload("Info.User").First(test) fmt.Println(test)}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
注意在嵌套子语句时上个子语句中的where在?上要加括号。
否则无法正常转译成嵌套sql语句