gorm创建和查询

GORM CRUD指南

演示实例

数据库初始化

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

type User struct {
    ID           int64 `gorm:"primary_key"`
    Name         string `gorm:"default:'靓仔'"`
    Age          int64
}

func main(){
    // 连接数据库
    db , err := gorm.Open("mysql",
        "root:root@(127.0.0.1:3306)/db1?charset=utf8&mb4&parseTime=True&loc=Local")
    if err != nil{
        panic( err )
        return
    }
    defer db.Close()
    fmt.Printf("连接数据库成功！\n")

    // 创建表(table)
    db.AutoMigrate(&User{})
    fmt.Printf("创建表成功！\n")

    // 添加元素
    // 01 - 05 : Charles,Mark,Bill,Vincent,William,
    // 06 - 10 : Jseph,James,Henry,Gary,Martin
    users := []User{
        {Name: "Charles", Age: 18},
        {Name: "Mark", Age: 40},
        {Name: "Bill", Age: 54},
        {Name: "Vincent", Age: 17},
        {Name: "William", Age: 59},
        {Name: "Jseph", Age: 50},
        {Name: "James", Age: 30},
        {Name: "Henry", Age: 17},
        {Name: "Gary", Age: 17},
        {Name: "Martin", Age: 95},
    }
    for _ , user := range users {
        db.Debug().Create(&user)
    }
}

高级查询

子查询

    //  高级查询
    //  子查询
    users = []User{}

    // SELECT * FROM `users`  WHERE ( name Like '%a%')
    db.Debug().Table("users").Select("*").Where(" name Like ?","%a%").Scan(&users)
    fmt.Printf("%#v\n",users)

    //  User{ID:1, Name:"Charles", Age:18},
    //  User{ID:2, Name:"Mark", Age:40},
    //  User{ID:5, Name:"William", Age:59},
    //  User{ID:7, Name:"James", Age:30},
    //  User{ID:9, Name:"Gary", Age:17},
    //  User{ID:10, Name:"Martin", Age:95}
    // ( 18 + 40 + 59 + 30 + 17 + 95 ) / 6 = 43

    //SELECT * FROM `users`  WHERE (age > (SELECT AVG(age) FROM `users`  WHERE ( name Like '%a%')) and name Like '%a%')
    //SELECT * FROM `users`  WHERE (age > 46) and name Like '%a%'
    db.Debug().Where("age > ? and name Like '%a%'",
        db.Table("users").
        Select("AVG(age)").
        Where(" name Like ?","%a%").
        SubQuery()).Find(&users)
    fmt.Printf("%#v\n",users)

    // Average = 43 ,
    //  User{ID:5, Name:"William", Age:59},
    //  User{ID:10, Name:"Martin", Age:95}}

选择字段

    //  高级查询
    //  选择字段

    //  SELECT name, age FROM `users`  WHERE (age = 40)
    users = []User{}
    db.Debug().Select("name, age").Find(&users,"age = 40")
    fmt.Printf("%#v\n",users)
    //  User{ID:0, Name:"Mark", Age:40}     (**注意**：ID字段是默认零值)

    //  SELECT name, age FROM `users`  WHERE (age = 17)
    users = []User{}
    db.Debug().Select([]string{"name", "age"}).Find(&users,"age = 17")
    fmt.Printf("%#v\n",users)
    //  User{ID:0, Name:"Vincent", Age:17},
    //  User{ID:0, Name:"Henry", Age:17}
    //  User{ID:0, Name:"Gary", Age:17} (**注意**：ID字段是默认零值)

排序

    //  排序
    //  SELECT * FROM `users`   ORDER BY age desc, name
    users = []User{}
    db.Debug().Order("age desc, name").Find(&users)
    fmt.Printf("%#v\n",users)
    //  User{ID:10, Name:"Martin", Age:95},
    //  User{ID:5, Name:"William", Age:59},
    //  User{ID:3, Name:"Bill", Age:54},
    //  User{ID:6, Name:"Jseph", Age:50},
    //  User{ID:2, Name:"Mark", Age:40},
    //  User{ID:7, Name:"James", Age:30},
    //  User{ID:1, Name:"Charles", Age:18},
    //  User{ID:8, Name:"Gary", Age:17},    *
    //  User{ID:9, Name:"Henry", Age:17},   **
    //  User{ID:4, Name:"Vincent", Age:17}  ***

    // 多字段排序
    //  SELECT * FROM `users`   ORDER BY age desc,`name`
    users = []User{}
    db.Debug().Order("age desc").Order("name desc").Find(&users)
    fmt.Printf("%#v\n",users)
    //  User{ID:10, Name:"Martin", Age:95},
    //  User{ID:5, Name:"William", Age:59},
    //  User{ID:3, Name:"Bill", Age:54},
    //  User{ID:6, Name:"Jseph", Age:50},
    //  User{ID:2, Name:"Mark", Age:40},
    //  User{ID:7, Name:"James", Age:30},
    //  User{ID:1, Name:"Charles", Age:18},
    //  User{ID:4, Name:"Vincent", Age:17}, ***
    //  User{ID:9, Name:"Henry", Age:17},   **
    //  User{ID:8, Name:"Gary", Age:17}     *



    // 覆盖排序
    //  Order,指定从数据库中检索出记录的顺序。设置第二个参数 reorder 为 true ，可以覆盖前面定义的排序条件。
    users1 := []User{}
    users2 := []User{}
    db.Debug().Order("age desc").Find(&users1).Debug().Order("age", true).Find(&users2)
    fmt.Printf("%#v\n%#v\n",users1,users2)
    //  User1   :
    //  User{ID:10, Name:"Martin", Age:95}
    //  User{ID:5, Name:"William", Age:59}
    //  User{ID:3, Name:"Bill", Age:54}
    //  User{ID:6, Name:"Jseph", Age:50}
    //  User{ID:2, Name:"Mark", Age:40}
    //  User{ID:7, Name:"James", Age:30}
    //  User{ID:1, Name:"Charles", Age:18}
    //  User{ID:4, Name:"Vincent", Age:17}  ***
    //  User{ID:8, Name:"Gary", Age:17}     *
    //  User{ID:9, Name:"Henry", Age:17}    **

    //  通过 User1 , User2 , 不是完全的倒置，默认有主键ID作为第二排序字;

    //  User2   :
    //  User{ID:4, Name:"Vincent", Age:17}  ***
    //  User{ID:8, Name:"Gary", Age:17}     *
    //  User{ID:9, Name:"Henry", Age:17}    **
    //  User{ID:1, Name:"Charles", Age:18}
    //  User{ID:7, Name:"James", Age:30}
    //  User{ID:2, Name:"Mark", Age:40}
    //  User{ID:6, Name:"Jseph", Age:50}
    //  User{ID:3, Name:"Bill", Age:54}
    //  User{ID:5, Name:"William", Age:59}
    //  User{ID:10, Name:"Martin", Age:95}

数量

    //  高级查询
    //  数量
    //  Limit，指定从数据库检索出的最大记录数。

    // SELECT * FROM `users`   LIMIT 3
    users = []User{}
    db.Debug().Limit(3).Find(&users)


    //  SELECT * FROM `users`   LIMIT 10
    //  SELECT * FROM `users`
    // -1 取消 Limit 条件
    users1 := []User{}
    users2 := []User{}
    db.Debug().Limit(10).Find(&users1).Debug().Limit(-1).Find(&users2)

偏移

    //  高级查询
    //  偏移
    //  Offset，指定开始返回记录前要跳过的记录数。

    //  SELECT * FROM `users`   LIMIT 5 OFFSET 3
    //  跳开前3个,然后往后取5条结果
    users = []User{}
    db.Debug().Offset(3).Limit(5).Find(&users)
    fmt.Printf("%#v\n",users)
    //  User{ID:4, Name:"Vincent", Age:17}
    //  User{ID:5, Name:"William", Age:59}
    //  User{ID:6, Name:"Jseph", Age:50}
    //  User{ID:7, Name:"James", Age:30}
    //  User{ID:8, Name:"Gary", Age:17}

    //  SELECT * FROM `users`   LIMIT 4 OFFSET 3
    //  跳开前3个,然后往后取4条结果
    users1 := []User{}
    db.Debug().Offset(3).Limit(4).Find(&users1)
    fmt.Printf("%#v\n",users1)
    //  User{ID:4, Name:"Vincent", Age:17}
    //  User{ID:5, Name:"William", Age:59}
    //  User{ID:6, Name:"Jseph", Age:50}
    //  User{ID:7, Name:"James", Age:30}


    // -1 取消 Offset 条件
    //  SELECT * FROM `users`   LIMIT 2
    //  不跳,然后往后取2条结果
    users2 := []User{}
    db.Debug().Offset(-1).Limit(2).Find(&users2)
    fmt.Printf("%#v\n",users2)
    //  User{ID:1, Name:"Charles", Age:18}
    //  User{ID:2, Name:"Mark", Age:40}

总数

    //  高级查询
    //  总数
    //  Count，该 model 能获取的记录总数


    //  SELECT * FROM `users`  WHERE (name = 'Vincent') OR (name = 'Gary')
    //  SELECT count(*) FROM `users`  WHERE (name = 'Vincent') OR (name = 'Gary')
    users = []User{}
    count := 0
    db.Debug().Where("name = ?", "Vincent").Or("name = ?", "Gary").Find(&users).Count(&count)
    fmt.Printf("%#v\n",users)
    fmt.Printf("%d\n",count)
    //  User{ID:4, Name:"Vincent", Age:17}
    //  User{ID:8, Name:"Gary", Age:17}}
    //  count = 2 

    //  SELECT count(*) FROM `users`  WHERE (name = 'William')
    count = 0
    db.Debug().Model(&User{}).Where("name = ?", "William").Count(&count)
    fmt.Printf("%d\n",count)
    //  count = 1

    //  SELECT count(*) FROM `users`
    count = 0
    db.Debug().Table("users").Count(&count)
    fmt.Printf("%d\n",count)
    //  count = 10

    //  SELECT count(distinct(age)) FROM `users`

    db.Debug().Table("users").Select("count(distinct(age))").Count(&count)
    fmt.Printf("%d\n",count)
    //  count = 8

注意 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

    //  SELECT age FROM `users`
    var ages []int64
    db.Debug().Find(&users).Pluck("age", &ages)
    fmt.Printf("%#v\n",ages)
    //  {18, 40, 54, 17, 59, 50, 30, 17, 17, 95}

    //  SELECT name FROM `users`
    var names []string
    db.Debug().Model(&User{}).Pluck("name", &names)
    fmt.Printf("%#v\n",names)
    //  {"Charles", "Mark", "Bill", "Vincent", "William", "Jseph", "James", "Gary", "Henry", "Martin"}

    names = []string{}
    db.Debug().Table("users").Pluck("name", &names)
    fmt.Printf("%#v\n",names)
    //  {"Charles", "Mark", "Bill", "Vincent", "William", "Jseph", "James", "Gary", "Henry", "Martin"}

    users = []User{}
    db.Debug().Select("name, age").Find(&users)
    fmt.Printf("%#v\n",users)
    //  只获取 Name 和 Age 字段
    //  User{ID:0, Name:"Charles", Age:18}
    //  User{ID:0, Name:"Mark", Age:40}
    //  User{ID:0, Name:"Bill", Age:54}
    //  User{ID:0, Name:"Vincent", Age:17}
    //  User{ID:0, Name:"William", Age:59}
    //  User{ID:0, Name:"Jseph", Age:50}
    //  User{ID:0, Name:"James", Age:30}
    //  User{ID:0, Name:"Gary", Age:17}
    //  User{ID:0, Name:"Henry", Age:17}
    //  User{ID:0, Name:"Martin", Age:95}

扫描

    //  高级查询
    //  Scan，扫描结果至一个 struct.
    //  SELECT id, name, age FROM `users`  WHERE (name = 'Henry')
    var result User
    db.Debug().Table("users").Select("id, name, age").Where("name = ?", "Henry").Scan(&result)
    fmt.Printf("%#v\n",result)
    //  User{ID:9, Name:"Henry", Age:17}


    //  SELECT id, name, age FROM `users`  WHERE (id > 7)
    var results []User
    db.Debug().Table("users").Select("id, name, age").Where("id > ?", 7).Scan(&results)
    fmt.Printf("%#v\n",results)
    //  User{ID:8, Name:"Gary", Age:17}
    //  User{ID:9, Name:"Henry", Age:17}
    //  User{ID:10, Name:"Martin", Age:95}

    //  原生 SQL
    //  SELECT id, name, age FROM users WHERE name = 'Vincent'
    result = User{}
    db.Debug().Raw("SELECT id, name, age FROM users WHERE name = ?", "Vincent").Scan(&result)
    fmt.Printf("%#v\n",result)
    //  User{ID:4, Name:"Vincent", Age:17}

链式操作相关

链式操作

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%'