查询进阶
第五章：查询进阶
5.1 高级 Where 条件
字符串条件
// 基本等于
db.Where("name = ?", "张三").Find(&users)
// 多条件
db.Where("name = ? AND age >= ?", "张三", 18).Find(&users)
// IN 查询
db.Where("name IN ?", []string{"张三", "李四", "王五"}).Find(&users)
// LIKE 模糊查询
db.Where("name LIKE ?", "%张%").Find(&users)
// BETWEEN
db.Where("age BETWEEN ? AND ?", 18, 30).Find(&users)
// 时间范围
db.Where("created_at > ?", time.Now().Add(-24*time.Hour)).Find(&users)
// IS NULL / IS NOT NULL
db.Where("email IS NOT NULL").Find(&users)
结构体条件
// 非零值字段参与查询
db.Where(&User{Name: "张三", Age: 0}).Find(&users)
// WHERE name = "张三" （Age=0 不参与）
// 指定字段查询
db.Where(&User{Name: "张三"}, "name").Find(&users)
// 只使用 name 字段
// 忽略某些字段
db.Where(&User{Name: "张三", Age: 25}).Find(&users, "Age")
// 忽略 Age 字段
Map 条件
db.Where(map[string]interface{}{"name": "张三", "age": 25}).Find(&users)
// WHERE name = "张三" AND age = 25
// 零值也会参与
db.Where(map[string]interface{}{"age": 0}).Find(&users)
// WHERE age = 0
智能条件选择
func BuildQuery(db *gorm.DB, req *UserQueryRequest) *gorm.DB {
if req.Name != "" {
db = db.Where("name LIKE ?", "%"+req.Name+"%")
}
if req.Age > 0 {
db = db.Where("age = ?", req.Age)
}
if req.Status > 0 {
db = db.Where("status = ?", req.Status)
}
if req.StartTime != nil && req.EndTime != nil {
db = db.Where("created_at BETWEEN ? AND ?", req.StartTime, req.EndTime)
}
return db
}
// 使用
var users []User
query := BuildQuery(db.Model(&User{}), &req)
query.Find(&users)
5.2 复杂逻辑组合
AND / OR 嵌套
// (name = '张三' OR name = '李四') AND age >= 18
db.Where(
db.Where("name = ?", "张三").Or("name = ?", "李四"),
).Where("age >= ?", 18).Find(&users)
// 等效于：
db.Where("(name = ? OR name = ?) AND age >= ?", "张三", "李四", 18).Find(&users)
复杂条件构建
// status = 1 AND (age < 18 OR age > 60)
db.Where("status = ?", 1).
Where(db.Where("age < ?", 18).Or("age > ?", 60)).
Find(&users)
// (name LIKE '%张%' OR email LIKE '%zhang%') AND age BETWEEN 18 AND 30
db.Where(
db.Where("name LIKE ?", "%张%").Or("email LIKE ?", "%zhang%"),
).Where("age BETWEEN ? AND ?", 18, 30).Find(&users)
使用原始 SQL
// 复杂条件用原生 SQL
db.Where("(name = ? AND age >= ?) OR (name = ? AND age <= ?)",
"张三", 18, "李四", 30).Find(&users)
// 使用 SQL 片段
db.Where("MATCH(name) AGAINST(? IN NATURAL LANGUAGE MODE)", "搜索词").Find(&users)
5.3 聚合函数
基础聚合
type Result struct {
Count int64
Sum float64
Avg float64
Min float64
Max float64
}
var result Result
// COUNT
db.Model(&User{}).Count(&result.Count)
// SUM
db.Model(&Order{}).Select("SUM(amount)").Scan(&result.Sum)
// AVG / MIN / MAX
db.Model(&Product{}).Select(
"AVG(price) as avg",
"MIN(price) as min",
"MAX(price) as max",
).Scan(&result)
分组聚合
type AgeGroup struct {
Age int
Count int64
}
var results []AgeGroup
db.Model(&User{}).
Select("age, COUNT(*) as count").
Group("age").
Find(&results)
// SELECT age, COUNT(*) as count FROM users GROUP BY age
// 带条件的分组
db.Model(&User{}).
Select("status, COUNT(*) as count, AVG(age) as avg_age").
Where("created_at > ?", lastMonth).
Group("status").
Find(&results)
Having 筛选
// 分组后筛选
db.Model(&User{}).
Select("age, COUNT(*) as count").
Group("age").
Having("count > ?", 10).
Find(&results)
// SELECT age, COUNT(*) as count FROM users GROUP BY age HAVING count > 10
// 多条件
db.Model(&Order{}).
Select("user_id, SUM(amount) as total").
Group("user_id").
Having("SUM(amount) > ?", 1000).
Having("COUNT(*) > ?", 5).
Find(&results)
5.4 联表查询
Joins
type UserWithCompany struct {
UserID uint
UserName string
CompanyName string
}
var results []UserWithCompany
// 内连接
db.Model(&User{}).
Select("users.id as user_id, users.name as user_name, companies.name as company_name").
Joins("JOIN companies ON companies.id = users.company_id").
Scan(&results)
// 左连接（保留所有用户）
db.Model(&User{}).
Select("users.id, users.name, companies.name as company_name").
Joins("LEFT JOIN companies ON companies.id = users.company_id").
Scan(&results)
// 多表连接
db.Table("orders o").
Select("o.id, u.name as user_name, p.name as product_name, o.amount").
Joins("JOIN users u ON u.id = o.user_id").
Joins("JOIN products p ON p.id = o.product_id").
Scan(&results)
关联 Join（预加载替代方案）
// 对于大表，可用 Joins 代替 Preload
db.Model(&User{}).
Joins("Company").
Where("users.name = ?", "张三").
Find(&users)
// 等效于 Preload，但使用 JOIN 语句
5.5 子查询
Where 子查询
// IN 子查询
db.Where("id IN (?)", db.Model(&Order{}).Select("user_id").Where("amount > ?", 100)).
Find(&users)
// SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100)
// EXISTS 子查询
db.Where("EXISTS (?)", db.Model(&Order{}).Select("1").Where("orders.user_id = users.id")).
Find(&users)
// SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
From 子查询
db.Table("(?) as u", db.Model(&User{}).Select("name", "age")).
Where("age > ?", 18).
Find(&results)
// SELECT * FROM (SELECT name, age FROM users) as u WHERE age > 18
复杂子查询
// 查询订单金额超过平均值的订单
subQuery := db.Model(&Order{}).Select("AVG(amount)")
db.Where("amount > (?)", subQuery).Find(&orders)
// 查询每个用户的最新订单
subQuery := db.Model(&Order{}).
Select("MAX(created_at)").
Where("user_id = orders.user_id")
db.Where("created_at = (?)", subQuery).Find(&latestOrders)
5.6 分页实现
基础分页
func Paginate(page, pageSize int) func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
if page <= 0 {
page = 1
}
if pageSize <= 0 {
pageSize = 10
}
offset := (page - 1) * pageSize
return db.Offset(offset).Limit(pageSize)
}
}
// 使用
db.Scopes(Paginate(page, pageSize)).Find(&users)
完整分页组件
type Pagination struct {
Page int `json:"page"`
PageSize int `json:"page_size"`
Total int64 `json:"total"`
TotalPages int `json:"total_pages"`
Data interface{} `json:"data"`
}
func (p *Pagination) Paginate(db *gorm.DB, dest interface{}) error {
if p.Page <= 0 {
p.Page = 1
}
if p.PageSize <= 0 {
p.PageSize = 10
}
if p.PageSize > 100 {
p.PageSize = 100 // 限制最大页大小
}
// 查询总数
var total int64
if err := db.Count(&total).Error; err != nil {
return err
}
p.Total = total
p.TotalPages = int((total + int64(p.PageSize) - 1) / int64(p.PageSize))
// 查询数据
offset := (p.Page - 1) * p.PageSize
return db.Offset(offset).Limit(p.PageSize).Find(dest).Error
}
// 使用示例
func GetUserList(ctx context.Context, req UserListRequest) (*Pagination, error) {
db := getDB().Model(&User{})
// 构建查询条件
if req.Keyword != "" {
db = db.Where("name LIKE ? OR email LIKE ?", "%"+req.Keyword+"%", "%"+req.Keyword+"%")
}
if req.Status > 0 {
db = db.Where("status = ?", req.Status)
}
// 排序
if req.SortBy != "" {
order := req.SortBy
if req.SortDesc {
order += " DESC"
}
db = db.Order(order)
} else {
db = db.Order("id DESC")
}
// 分页
pagination := &Pagination{
Page: req.Page,
PageSize: req.PageSize,
}
var users []User
if err := pagination.Paginate(db, &users); err != nil {
return nil, err
}
pagination.Data = users
return pagination, nil
}
游标分页（高性能）
func CursorPaginate(db *gorm.DB, lastID uint, pageSize int, dest interface{}) (nextID uint, err error) {
if pageSize <= 0 || pageSize > 100 {
pageSize = 20
}
query := db.Where("id > ?", lastID).Order("id").Limit(pageSize)
if err = query.Find(dest).Error; err != nil {
return 0, err
}
// 获取最后一条记录的 ID
// 这里需要反射获取最后一条记录的 ID
return nextID, nil
}
5.7 原生 SQL
查询
// 简单查询
type Result struct {
ID int
Name string
}
var result Result
db.Raw("SELECT id, name FROM users WHERE id = ?", 1).Scan(&result)
// 多行
type UserStat struct {
Status int
Count int64
}
var stats []UserStat
db.Raw("SELECT status, COUNT(*) as count FROM users GROUP BY status").Scan(&stats)
// 命名参数
db.Raw("SELECT * FROM users WHERE name = @name AND age = @age",
sql.Named("name", "张三"),
sql.Named("age", 25),
).Scan(&users)
执行
// 执行更新
db.Exec("UPDATE users SET age = age + ? WHERE name = ?", 1, "张三")
// 批量插入
db.Exec("INSERT INTO users (name, age) VALUES (?, ?), (?, ?)",
"张三", 25, "李四", 28)
// 使用 Exec 执行 Upsert（MySQL）
db.Exec(`
INSERT INTO users (id, name, age)
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age)
`, 1, "张三", 25)
5.8 完整查询示例
package main
import (
"fmt"
"gorm.io/driver/sqlite"
"gorm.io/gorm"
)
type User struct {
ID uint
Name string
Age int
Email string
Status int
CreatedAt int64
}
func main() {
db, _ := gorm.Open(sqlite.Open("file::memory:?cache=shared"), &gorm.Config{})
db.AutoMigrate(&User{})
// 插入测试数据
for i := 1; i <= 100; i++ {
db.Create(&User{
Name: fmt.Sprintf("User%d", i),
Age: 18 + i%40,
Email: fmt.Sprintf("user%d@example.com", i),
Status: i % 3,
})
}
// ===== 高级查询示例 =====
// 1. 复杂条件
var users []User
db.Where("age BETWEEN ? AND ?", 20, 30).
Where("status = ?", 1).
Where("name LIKE ?", "%User%").
Find(&users)
fmt.Printf("条件查询: %d 条\n", len(users))
// 2. OR 条件
db.Where("age < ?", 20).Or("age > ?", 50).Find(&users)
fmt.Printf("OR 条件: %d 条\n", len(users))
// 3. 分页
var pageUsers []User
db.Order("age DESC").Offset(0).Limit(10).Find(&pageUsers)
fmt.Printf("第一页: %v\n", pageUsers[0].Age)
// 4. 聚合
type AggResult struct {
Status int
Count int64
AvgAge float64
}
var agg []AggResult
db.Model(&User{}).
Select("status, COUNT(*) as count, AVG(age) as avg_age").
Group("status").
Find(&agg)
fmt.Printf("聚合结果: %+v\n", agg)
// 5. 子查询
db.Where("age > (?)", db.Model(&User{}).Select("AVG(age)")).Find(&users)
fmt.Printf("高于平均年龄: %d 条\n", len(users))
}
5.9 练习题
- 实现一个通用的条件查询构建器，支持动态 AND/OR 组合
- 编写一个统计报表查询，包含分组、聚合、Having 筛选
- 对比 Offset/Limit 分页和游标分页在大数据量下的性能差异
5.10 小结
本章深入讲解了 GORM 的高级查询功能，包括复杂条件、聚合函数、联表查询、子查询和分页实现。掌握这些技巧可以应对绝大多数业务查询需求。
本文代码地址：https://github.com/LittleMoreInteresting/gorm_study
欢迎关注公众号，一起学习进步！
