简介
前置代码
package main
import (
"database/sql"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/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)
}
}
检索单个对象
GORM 提供了 First、Take、Last 方法,以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1 条件,且没有找到记录时,它会返回 ErrRecordNotFound 错误
var user User
// 获取第一条记录(主键升序)
db.First(&user)
// 获取一条记录,没有指定排序字段
db.Take(&user)
// 获取最后一条记录(主键降序)
db.Last(&user)
result := db.First(&user)
// 1
fmt.Println(result.RowsAffected) // 返回找到的记录数
// <nil>
fmt.Println(result.Error) // returns error or nil
// 检查 ErrRecordNotFound 错误
// false
fmt.Println(errors.Is(result.Error, gorm.ErrRecordNotFound))
日志:
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` = 1 LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` = 1 ORDER BY `users`.`id` DESC LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` = 1 ORDER BY `users`.`id` LIMIT 1
First 和 Last 会根据主键排序,分别查询第一条和最后一条记录。 只有在目标 struct 是指针或者通过 db.Model() 指定 model 时,该方法才有效。 此外,如果相关 model 没有定义主键,那么将按 model 的第一个字段进行排序。
type Language struct {
Code string
Name string
}
_ = db.AutoMigrate(&Language{})
// 没有设置主键根据第一个字段Code进行排序
db.First(&Language{})
CREATE TABLE `languages` (`code` longtext,`name` longtext)
SELECT * FROM `languages` ORDER BY `languages`.`code` LIMIT 1
用主键检索
var user User
var users []User
db.First(&user, 10)
var user1 User
db.First(&user1, "10")
db.Find(&users, []int{1, 2, 3})
日志:
SELECT * FROM `users` WHERE `users`.`id` = 10 ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` = '10' ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` IN (1,2,3)
主键uint类型,但是传入字符串GORM内部会做一个转换,不影响正常查询
检索全部对象
var users []User
result := db.Find(&users)
// 22
fmt.Println(result.RowsAffected) // 返回找到的记录数
// <nil>
fmt.Println(result.Error)
日志:
SELECT * FROM `users`
条件
String 条件
var user User
var users []User
db.Where("name = ?", "jinzhu").First(&user)
db.Where("name <> ?", "jinzhu").Find(&users)
// IN
db.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)
// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
// Time
db.Where("updated_at > ?", time.Date(2022,05,20,0,0,0,0,&time.Location{})).Find(&users)
// BETWEEN
db.Where("created_at BETWEEN ? AND ?", time.Date(2022,05,20,0,0,0,0,&time.Location{}), time.Now()).Find(&users)
日志:
SELECT * FROM `users` WHERE name = 'jinzhu' ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE name <> 'jinzhu'
SELECT * FROM `users` WHERE name IN ('jinzhu','jinzhu 2')
SELECT * FROM `users` WHERE name LIKE '%jin%'
SELECT * FROM `users` WHERE name = 'jinzhu' AND age >= '22'
SELECT * FROM `users` WHERE updated_at > '2022-05-20 00:00:00'
SELECT * FROM `users` WHERE created_at BETWEEN '2022-05-20 00:00:00' AND '2022-05-26 21:05:32.957'
Struct & Map 条件
var user User
var users []User
// Struct
db.Where(&User{Name: "jinzhu", Age: 0}).First(&user)
// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 0}).Find(&users)
// Slice of primary keys
db.Where([]int64{20, 21, 22}).Find(&users)
日志:
SELECT * FROM `users` WHERE `users`.`name` = 'jinzhu' ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `age` = 0 AND `name` = 'jinzhu'
SELECT * FROM `users` WHERE `users`.`id` IN (20,21,22)
第一条SQL和第二条SQL的查询条件都是一样的;但是第一条SQL就没有使用Age=0这个条件
注意使用 struct 查询时,GORM 只会查询非零字段,这意味着如果您的字段的值为0,或其他零值’',则不会用于构建查询条件,要在查询条件中包含零值可以使用map
指定结构体查询字段
使用 struct 进行搜索时,您可以通过将相关字段名或 dbname 传递给 来指定要在查询条件中使用的 struct 中的哪些特定值Where()
var users []User
db.Where(&User{Name: "jinzhu" }, "name" , "Age" ).Find(&users)
db.Where(&User{Name: "jinzhu" }, "Age" ).Find(&users)
日志:
SELECT * FROM `users` WHERE `users`.`name` = 'jinzhu' AND `users`.`age` = 0
SELECT * FROM `users` WHERE `users`.`age` = 0
内联条件
查询条件可以内联到方法中,First例如.FindWhere
var users []User
db.First(&user, "id = ?", "string_primary_key")
// Plain SQL
db.Find(&user, "name = ?", "jinzhu")
db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)
// Struct
db.Find(&users, User{Age: 20})
// Map
db.Find(&users, map[string]interface{}{"age": 20})
日志:
SELECT * FROM `users` WHERE id = 'string_primary_key' ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE name = 'jinzhu'
SELECT * FROM `users` WHERE name <> 'jinzhu' AND age > 20
SELECT * FROM `users` WHERE `users`.`age` = 20
SELECT * FROM `users` WHERE `age` = 20
Not 条件
var users []User
db.Not("name = ?", "jinzhu").First(&user)
// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// Struct
db.Not(User{Name: "jinzhu", Age: 18}).First(&user)
db.Not([]int64{1,2,3}).First(&user)
日志:
SELECT * FROM `users` WHERE NOT name = 'jinzhu' ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `name` NOT IN ('jinzhu','jinzhu 2')
SELECT * FROM `users` WHERE (`users`.`name` <> 'jinzhu' AND `users`.`age` <> 18) AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
SELECT * FROM `users` WHERE `users`.`id` NOT IN (1,2,3) AND `users`.`id` = 5 ORDER BY `users`.`id` LIMIT 1
Or 条件
var users []User
db.Where("name = ?", "jinzhu1").Or("age = ?", 18).Find(&users)
// Struct
db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)
// Map
db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)
日志:
SELECT * FROM `users` WHERE name = 'jinzhu1' OR age = 18
SELECT * FROM `users` WHERE name = 'jinzhu' OR (`users`.`name` = 'jinzhu 2' AND `users`.`age` = 18)
SELECT * FROM `users` WHERE name = 'jinzhu' OR (`age` = 18 AND `name` = 'jinzhu 2')
选择特定字段
var users []User
db.Select("name", "age").Find(&users)
db.Select([]string{"name", "age"}).Find(&users)
db.Table("users").Select("COALESCE(age,?)", 18).Rows()
日志:
SELECT `name`,`age` FROM `users`
SELECT `name`,`age` FROM `users`
SELECT COALESCE(age,18) FROM `users`
Order
var users []User
db.Order("age desc, name").Find(&users)
db.Order("age desc").Order("name").Find(&users)
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 age desc, name
SELECT * FROM `users` ORDER BY age desc,name
SELECT * FROM `users` ORDER BY FIELD(id,1,2,3)
Limit & Offset
Limit指定要检索的最大记录数Offset指定在开始返回记录之前要跳过的记录数
var users []User
var users1 []User
var users2 []User
db.Limit(3).Find(&users)
db.Limit(10).Find(&users1).Limit(-1).Find(&users2)
db.Limit(10).Offset(3).Find(&users)
db.Limit(10).Offset(5).Find(&users)
db.Limit(10).Offset(10).Find(&users1).Limit(10).Offset(-1).Find(&users2)
日志:
SELECT * FROM `users` LIMIT 3
SELECT * FROM `users` LIMIT 10
SELECT * FROM `users`
SELECT * FROM `users` LIMIT 10 OFFSET 3
SELECT * FROM `users` LIMIT 10 OFFSET 5
SELECT * FROM `users` LIMIT 10 OFFSET 10
SELECT * FROM `users` LIMIT 10
Distinct
var results []User
db.Distinct("name", "age").Order("name, age desc").Find(&results)
日志:
SELECT DISTINCT `name`,`age` FROM `users` ORDER BY name, age desc