gorm系列-查询
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%'
上一篇: 浮点数精度上误差
下一篇: GB码和BIG5码的互换技术