介绍
上文 gorm基础03–CRUD 接口-创建 介绍了gorm 中创建表和记录的多种方法,本文继续介绍gorm 中常见的查询方法。具体包括 检索单个对象、用主键检索、检索全部对象、通过字符串条件搜索、通过Struct|map条件搜索 等。
案例
检索单个对象 searchSingle
用主键检索 searchByPrimaryKey
检索全部对象 searchAll
通过字符串条件搜索 searchStrCondition
通过Struct|map条件搜索 searchStructMap
源码:
package main
import (
"database/sql"
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/schema"
"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 getDb() *gorm.DB {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:111111@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{NamingStrategy: schema.NamingStrategy{SingularTable: true}})
fmt.Printf("getDb db=%v, err=%v\n", db, err)
return db
}
func searchSingle(db *gorm.DB) {
var (
user User
user2 User
user3 User
)
// 获取第一条记录(主键升序)
result := db.First(&user)
fmt.Printf("RowsAffected=%v, %v\n", result.RowsAffected, result.Error)
fmt.Printf("user=%v\n", user)
// SELECT * FROM user ORDER BY id LIMIT 1;
// 获取一条记录,没有指定排序字段
result2 := db.Take(&user2)
fmt.Printf("RowsAffected=%v, %v\n", result2.RowsAffected, result2.Error)
fmt.Printf("user2=%v\n", user2)
// SELECT * FROM user LIMIT 1;
// 获取最后一条记录(主键降序)
result3 := db.Last(&user3)
fmt.Printf("RowsAffected=%v, %v\n", result3.RowsAffected, result3.Error)
fmt.Printf("user3=%v\n", user3)
// SELECT * FROM user ORDER BY id DESC LIMIT 1;
}
func searchByPrimaryKey(db *gorm.DB) {
var user User
var users []User
result := db.First(&user, 10)
fmt.Printf("RowsAffected=%v, %v\n", result.RowsAffected, result.Error)
fmt.Printf("user=%v\n", user)
// SELECT * FROM users WHERE id = 10;
result2 := db.First(&user, "10")
fmt.Printf("RowsAffected=%v, %v\n", result2.RowsAffected, result2.Error)
fmt.Printf("user=%v\n", users)
// SELECT * FROM users WHERE id = 10;
result3 := db.Find(&users, []int{1, 2, 3})
fmt.Printf("RowsAffected=%v, %v\n", result3.RowsAffected, result3.Error)
fmt.Printf("user=%v\n", users)
// SELECT * FROM users WHERE id IN (1,2,3);
}
func searchAll(db *gorm.DB) {
var users []User
// 获取全部记录
result := db.Find(&users)
// SELECT * FROM users;
fmt.Printf("RowsAffected=%v, %v\n", result.RowsAffected, result.Error)
fmt.Printf("users=%v\n", users)
// SELECT * FROM users WHERE id = 10;
}
func searchStrCondition(db *gorm.DB) {
var user User
var users []User
// 获取第一条匹配的记录
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;
lastWeek := time.Now().AddDate(0, 0, -7)
today := time.Now()
// Time
fmt.Printf("lastWeek=%v, today=%v\n", lastWeek, today)
result := db.Where("updated_at > ?", lastWeek).Find(&users)
fmt.Printf("RowsAffected=%v,%v\n", result.RowsAffected, result.Error)
fmt.Printf("users=%v", 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';
}
func searchStructMap(db *gorm.DB) {
var user User
var users []User
// Struct
result := db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;
fmt.Printf("RowsAffected=%v,%v\n", result.RowsAffected, result.Error)
fmt.Printf("struct:%v\n", user)
// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;
fmt.Printf("map:%v\n", users)
// 主键切片条件
db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM users WHERE id IN (20, 21, 22);
fmt.Printf("primaryKeySlice:%v", users)
}
func main() {
db := getDb()
//searchSingle(db)
//searchByPrimaryKey(db)
//searchAll(db)
//searchStrCondition(db)
searchStructMap(db)
}
测试:
searchSingle:
RowsAffected=1, <nil>
user={1 Jinzhu 18 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST}
RowsAffected=1, <nil>
user2={1 Jinzhu 18 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST}
RowsAffected=1, <nil>
user3={13 map_2.2 20 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC}
searchByPrimaryKey:
RowsAffected=1, <nil>
user={10 jinzhu_104 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST}
RowsAffected=1, <nil>
user=[]
RowsAffected=3, <nil>
user=[{1 Jinzhu 18 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {2 Jinzhu 18 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {3 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 2022-04-13 00:29:31 +0800 CST}]
searchAll:
RowsAffected=13, <nil>
users=[{1 Jinzhu 18 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {2 Jinzhu 18 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {3 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 2022-04-13 00:29:31 +0800 CST} {4 jinzhu1 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {5 jinzhu2 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {6 jinzhu3 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {7 jinzhu_101 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {8 jinzhu_102 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {9 jinzhu_103 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {10 jinzhu_104 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {11 map_1 18 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC} {12 map_2.1 18 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC} {13 map_2.2 20 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC}]
searchStrCondition:
lastWeek=2022-04-08 00:41:38.885205311 +0800 CST, today=2022-04-15 00:41:38.885206211 +0800 CST m=+0.004170521
RowsAffected=10,<nil>
users=[{1 Jinzhu 18 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {2 Jinzhu 18 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {3 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 2022-04-13 00:29:31 +0800 CST} {4 jinzhu1 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {5 jinzhu2 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {6 jinzhu3 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {7 jinzhu_101 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {8 jinzhu_102 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {9 jinzhu_103 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST} {10 jinzhu_104 0 2022-04-13 00:29:31 +0800 CST { false} {0001-01-01 00:00:00 +0000 UTC false} 2022-04-13 00:29:31 +0800 CST 2022-04-13 00:29:31 +0800 CST}]
searchStructMap:
RowsAffected=0,record not found
struct:{0 0 0001-01-01 00:00:00 +0000 UTC { false} {0001-01-01 00:00:00 +0000 UTC false} 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC}
map:[]
primaryKeySlice:[]