gorm系列-查询
程序员文章站
2022-06-27 20:23:50
[TOC] Gorm查询 一般查询 go package main import ( "database/sql" "fmt" "github.com/jinzhu/gorm" _ "github.com/jinzhu/gorm/dialects/mysql" ) //1. 定义模型 type Us ......
目录
gorm查询
一般查询
// 根据主键查询第一条记录 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; // 查询所有的记录 db.find(&users) //// select * from users; // 查询指定的某条记录(仅当主键为整型时可用) db.first(&user, 10) //// select * from users where id = 10;
package main
import (
"database/sql"
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
//1. 定义模型
type user struct {
id int64
//name *string `gorm:"default:'zisefeizhu'"`
name sql.nullstring `gorm:"default:'zisefeizhu'"`
age byte
}
func main() {
//2. 连接mysql数据库
db, err := gorm.open("mysql","root:123456@tcp(127.0.0.1:3306)/db?charset=utf8mb4&parsetime=true&loc=local")
if err != nil {
panic(err)
}
defer db.close()
//3. 把模型与数据库中的表对应起来
db.automigrate(&user{})
//4. 创建结构体实例
//u := user{name: sql.nullstring{"", false}, age: 60}
//fmt.println(db.newrecord(&u)) // 主键为空返回`true`
//db.debug().create(&u) // 在数据库中创建一条user记录
//fmt.println(db.newrecord(u)) // 创建`user`后返回`false`
//查询
//var user user //声明模型结构体变量类型user (文件夹a)
//db.first(&user) //（文件夹b）
//user:main.user{id:1, name:sql.nullstring{string:"zisefeizhu", valid:true}, age:0x1e}
user := new(user)
db.first(user)
fmt.printf("user:%#v\n",user)
//user:&main.user{id:1, name:sql.nullstring{string:"zisefeizhu", valid:true}, age:0x1e}
//查询所有记录
var users []user
//users := make([]user, 10)
db.find(&users)
fmt.printf("user:%#v\n",users)
}
where条件
普通sql查询
/ get first matched record
db.where("name = ?", "jinzhu").first(&user)
// select * from users where name = 'jinzhu' limit 1;
// get all matched records
db.where("name = ?", "jinzhu").find(&users)
// select * from users where name = 'jinzhu';
// <>
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';
//查询where记录
var users []user
db.where("name = ?", "zisefeizhu ").find(&users)
fmt.printf("user:%#v\n",users)
struch & map 查询
// struct
db.where(&user{name: "jinzhu", age: 20}).first(&user)
// select * from users where name = "jinzhu" and age = 20 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);
注意：当通过结构体进行查询时，gorm将会只通过非零值字段查询，这意味着如果你的字段值为0，''，false或者其他零值时，将不会被用于构建查询条件。
db.where(&user{name: "jinzhu", age: 0}).find(&users)
// select * from users where name = "jinzhu";
可以使用指针或实现 scanner/valuer 接口来避免这个问题
// 使用指针
type user struct {
gorm.model
name string
age *int
}
// 使用 scanner/valuer
type user struct {
gorm.model
name string
age sql.nullint64 // sql.nullint64 实现了 scanner/valuer 接口
}
not条件
作用与 where 类似的情形
db.not("name", "jinzhu").first(&user)
//// select * from users where name <> "jinzhu" limit 1;
// not in
db.not("name", []string{"jinzhu", "jinzhu 2"}).find(&users)
//// select * from users where name not in ("jinzhu", "jinzhu 2");
// not in slice of primary keys
db.not([]int64{1,2,3}).first(&user)
//// select * from users where id not in (1,2,3);
db.not([]int64{}).first(&user)
//// select * from users;
// plain sql
db.not("name = ?", "jinzhu").first(&user)
//// select * from users where not(name = "jinzhu");
// struct
db.not(user{name: "jinzhu"}).first(&user)
//// select * from users where name <> "jinzhu";
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"}).find(&users)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2';
// map
db.where("name = 'jinzhu'").or(map[string]interface{}{"name": "jinzhu 2"}).find(&users)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2';
内联条件
作用与where查询类似，当内联条件与多个立即执行方法一起使用时, 内联条件不会传递给后面的立即执行方法。
// 根据主键获取记录 (只适用于整形主键)
db.first(&user, 23)
//// select * from users where id = 23 limit 1;
// 根据主键获取记录, 如果它是一个非整形主键
db.first(&user, "id = ?", "string_primary_key")
//// select * from users where id = 'string_primary_key' limit 1;
// 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;
额外查询选项
// 为查询 sql 添加额外的 sql 操作
db.set("gorm:query_option", "for update").first(&user, 10)
//// select * from users where id = 10 for update;
firstorinit
获取匹配的第一条记录，否则根据给定的条件初始化一个新的对象 (仅支持 struct 和 map 条件)
//查询firstorinit记录
var user user
db.firstorinit(&user,user{name: "小王子"})
fmt.printf("user:%#v\n",user)
//user:main.user{id:0, name:"小王子", age:0x15}
attrs
如果记录未找到，将使用参数初始化 struct.
// 未找到
db.where(user{name: "non_existing"}).attrs(user{age: 20}).firstorinit(&user)
//// select * from users where name = 'non_existing';
//// user -> user{name: "non_existing", age: 20}
db.where(user{name: "non_existing"}).attrs("age", 20).firstorinit(&user)
//// select * from users where name = 'non_existing';
//// user -> user{name: "non_existing", age: 20}
// 找到
db.where(user{name: "jinzhu"}).attrs(user{age: 30}).firstorinit(&user)
//// select * from users where name = jinzhu';
//// user -> user{id: 111, name: "jinzhu", age: 20}
assign
不管记录是否找到，都将参数赋值给 struct.
// 未找到
db.where(user{name: "non_existing"}).assign(user{age: 20}).firstorinit(&user)
//// user -> user{name: "non_existing", age: 20}
// 找到
db.where(user{name: "jinzhu"}).assign(user{age: 30}).firstorinit(&user)
// select * from users where name = jinzhu';
// user -> user{id: 111, name: "jinzhu", age: 30}
firstorcreate
获取匹配的第一条记录, 否则根据给定的条件创建一个新的记录 (仅支持 struct 和 map 条件)
// 未找到
db.firstorcreate(&user, user{name: "non_existing"})
//// insert into "users" (name) values ("non_existing");
//// user -> user{id: 112, name: "non_existing"}
// 找到
db.where(user{name: "jinzhu"}).firstorcreate(&user)
//// user -> user{id: 111, name: "jinzhu"}
attrs
如果记录未找到，将使用参数创建 struct 和记录.
// 未找到
db.where(user{name: "non_existing"}).attrs(user{age: 20}).firstorcreate(&user)
//// select * from users where name = 'non_existing';
//// insert into "users" (name, age) values ("non_existing", 20);
//// user -> user{id: 112, name: "non_existing", age: 20}
// 找到
db.where(user{name: "jinzhu"}).attrs(user{age: 30}).firstorcreate(&user)
//// select * from users where name = 'jinzhu';
//// user -> user{id: 111, name: "jinzhu", age: 20}
assign
不管记录是否找到，都将参数赋值给 struct 并保存至数据库.
// 未找到
db.where(user{name: "non_existing"}).assign(user{age: 20}).firstorcreate(&user)
//// select * from users where name = 'non_existing';
//// insert into "users" (name, age) values ("non_existing", 20);
//// user -> user{id: 112, name: "non_existing", age: 20}
// 找到
db.where(user{name: "jinzhu"}).assign(user{age: 30}).firstorcreate(&user)
//// select * from users where name = 'jinzhu';
//// update users set age=30 where id = 111;
//// user -> user{id: 111, name: "jinzhu", age: 30}
高级查询
子查询
基于 *gorm.expr 的子查询
db.where("amount > ?", db.table("orders").select("avg(amount)").where("state = ?", "paid").subquery()).find(&orders)
// select * from "orders" where "orders"."deleted_at" is null and (amount > (select avg(amount) from "orders" where (state = 'paid')));
选择字段
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;
排序
order，指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ，可以覆盖前面定义的排序条件。
db.order("age desc, name").find(&users)
//// select * from users order by age desc, name;
// 多字段排序
db.order("age desc").order("name").find(&users)
//// select * from users order by age desc, name;
// 覆盖排序
db.order("age desc").find(&users1).order("age", true).find(&users2)
//// select * from users order by age desc; (users1)
//// select * from users order by age; (users2)
数量
limit，指定从数据库检索出的最大记录数。
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)
偏移
offset，指定开始返回记录前要跳过的记录数。
db.offset(3).find(&users) //// select * from users offset 3; // -1 取消 offset 条件 db.offset(10).find(&users1).offset(-1).find(&users2) //// select * from users offset 10; (users1) //// select * from users; (users2)
总数
count，该 model 能获取的记录总数
db.where("name = ?", "jinzhu").or("name = ?", "jinzhu 2").find(&users).count(&count)
//// select * from users where name = 'jinzhu' or name = 'jinzhu 2'; (users)
//// select count(*) from users where name = 'jinzhu' or name = 'jinzhu 2'; (count)
db.model(&user{}).where("name = ?", "jinzhu").count(&count)
//// select count(*) from users where name = 'jinzhu'; (count)
db.table("deleted_users").count(&count)
//// select count(*) from deleted_users;
db.table("deleted_users").select("count(distinct(name))").count(&count)
//// select count( distinct(name) ) from deleted_users; (count)
注意：count 必须是链式查询的最后一个操作 ，因为它会覆盖前面的 select，但如果里面使用了 count 时不会覆盖
group & having
rows, err := db.table("orders").select("date(created_at) as date, sum(amount) as total").group("date(created_at)").rows()
for rows.next() {
...
}
// 使用scan将多条结果扫描进事先准备好的结构体切片中
type result struct {
date time.time
total int
}
var rets []result
db.table("users").select("date(created_at) as date, sum(age) as total").group("date(created_at)").scan(&rets)
rows, err := db.table("orders").select("date(created_at) as date, sum(amount) as total").group("date(created_at)").having("sum(amount) > ?", 100).rows()
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)
连接
joins，指定连接条件
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_cards.user_id = users.id").where("credit_cards.number = ?", "411111111111").find(&user)
pluck
pluck，查询 model 中的一个列作为切片，如果您想要查询多个列，您应该使用 scan
var ages []int64
db.find(&users).pluck("age", &ages)
var names []string
db.model(&user{}).pluck("name", &names)
db.table("deleted_users").pluck("name", &names)
// 想查询多个字段？ 这样做：
db.select("name, age").find(&users)
扫描
scan，扫描结果至一个 struct.
type result struct {
name string
age int
}
var result result
db.table("users").select("name, age").where("name = ?", "antonio").scan(&result)
var results []result
db.table("users").select("name, age").where("id > ?", 0).scan(&results)
// 原生 sql
db.raw("select name, age from users where name = ?", "antonio").scan(&result)
链式操作相关
链式操作
method chaining，gorm 实现了链式操作接口，所以你可以把代码写成这样：
// 创建一个查询
tx := db.where("name = ?", "jinzhu")
// 添加更多条件
if somecondition {
tx = tx.where("age = ?", 20)
} else {
tx = tx.where("age = ?", 30)
}
if yetanothercondition {
tx = tx.where("active = ?", 1)
}
在调用立即执行方法前不会生成query语句，借助这个特性你可以创建一个函数来处理一些通用逻辑。
立即执行方法
immediate methods ，立即执行方法是指那些会立即生成sql语句并发送到数据库的方法, 他们一般是crud方法，比如：
create, first, find, take, save, updatexxx, delete, scan, row, rows…
这有一个基于上面链式方法代码的立即执行方法的例子
tx.find(&user)
生成的sql语句如下：
select * from users where name = 'jinzhu' and age = 30 and active = 1;
范围
scopes，scope是建立在链式操作的基础之上的。
基于它，你可以抽取一些通用逻辑，写出更多可重用的函数库。
func amountgreaterthan1000(db *gorm.db) *gorm.db {
return db.where("amount > ?", 1000)
}
func paidwithcreditcard(db *gorm.db) *gorm.db {
return db.where("pay_mode_sign = ?", "c")
}
func paidwithcod(db *gorm.db) *gorm.db {
return db.where("pay_mode_sign = ?", "c")
}
func orderstatus(status []string) func (db *gorm.db) *gorm.db {
return func (db *gorm.db) *gorm.db {
return db.scopes(amountgreaterthan1000).where("status in (?)", status)
}
}
db.scopes(amountgreaterthan1000, paidwithcreditcard).find(&orders)
// 查找所有金额大于 1000 的信用卡订单
db.scopes(amountgreaterthan1000, paidwithcod).find(&orders)
// 查找所有金额大于 1000 的 cod 订单
db.scopes(amountgreaterthan1000, orderstatus([]string{"paid", "shipped"})).find(&orders)
// 查找所有金额大于 1000 且已付款或者已发货的订单
多个立即执行方法
multiple immediate methods，在 gorm 中使用多个立即执行方法时，后一个立即执行方法会复用前一个立即执行方法的条件 (不包括内联条件) 。
db.where("name like ?", "jinzhu%").find(&users, "id in (?)", []int{1, 2, 3}).count(&count)
生成的 sql
select * from users where name like 'jinzhu%' and id in (1, 2, 3) select count(*) from users where name like 'jinzhu%'