Gorm使用详解

1.什么是Gorm

go语言编写的orm框架

特点：

1)全功能ORM

2)关联（包含一个，包含多个，属于，多对多）

3)Callbacks（创建/保存/更新/删除/查找前后回调）

4)预加载

5)事务

6)复合主键

7)SQL Builder（执行原生sql）

8)自动迁移

9)日志

2.安装

go get -u github.com/jinzhu/gorm

3.数据库连接

1)引入相应驱动

import _ "github.com/jinzhu/gorm/dialects/mysql"

// import _ "github.com/jinzhu/gorm/dialects/postgres"

// import _ "github.com/jinzhu/gorm/dialects/sqlite"

// import _ "github.com/jinzhu/gorm/dialects/mssql"

2)连接示例

Mysql

import (

    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/mysql"

)

func main() {

  db, err := gorm.Open("mysql", "user:password@/dbname?charset=utf8&parseTime=True&loc=Local")

  defer db.Close()

}

PostgreSQL

import (

    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/postgres"

)

func main() {

  db, err := gorm.Open("postgres", "host=myhost user=gorm dbname=gorm sslmode=disable password=mypassword")

  defer db.Close()

}

Sqlite3

import (

    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/sqlite"

)

func main() {

  db, err := gorm.Open("sqlite3", "/tmp/gorm.db")

  defer db.Close()

}

4.自动迁移

创建表，缺少的列和索引，不会改变现有列的类型或删除列

package main

import (

	"fmt"

	"github.com/jinzhu/gorm"

	_ "github.com/jinzhu/gorm/dialects/postgres"

	"time"

)

const (

	host = "127.0.0.1"

	port = 5432

	user = "postgres"

	password = "1"

	dbname = "dm-identity"

)

type User struct {

	gorm.Model

	Name string

	Age int

	Birthday time.Time

}

func main() {

	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)

	db, err := gorm.Open("postgres", psqlInfo)

	if err != nil{

		panic(err)

	}else{

		fmt.Println("connected successfully")

	}

	defer db.Close()

	db.AutoMigrate(&User{})

}

db.AutoMigrate(&User{})会创建/更新"users"表。

5.表结构操作

1)检验表是否存在

var result bool

result = db.HasTable(&User{})

fmt.Println(result)

运行结果：

true

2)创建表

db.CreateTable(&User{})

3)删除表

// 删除模型User对应的表

db.DropTable(&User{})

// 删除users表

db.DropTable("users")

4)修改列

db.Model(&User{}).ModifyColumn("description", "text")

5)删除列

db.model(&User{}).DropColumn("description")

6)添加外键

db.Model(&User{}).AddForeignKey("city_id","cities(id)")

7)添加索引

db.Model(&User{}).AddIndex("idx_user_name", "name")

db.Model(&User{]).AddUniqueIndex("idx_user_name", "name")

8)删除索引

db.Model(&User{}).RemoveIndex("idx_user_name")

6.模型Model定义

1)gorm.Model结构体

type Model struct {

	ID        uint `gorm:"primary_key"`

	CreatedAt time.Time

	UpdatedAt time.Time

	DeletedAt *time.Time `sql:"index"`

}

2)定义Model结构体

// 默认表名是 Model名称的小写+复数

type Profile struct {

	gorm.Model

	Refer int

	Name  string

}

type User struct {

  gorm.Model

  Name string  `gorm:"size:255"` //string默认长度255,size重设长度

  Age int `gorm:"column:my_age"` //设置列名为my_age

  Num int  `gorm:"AUTO_INCREMENT"` //自增

  IgnoreMe int `gorm:"-"` // 忽略字段

  Email string `gorm:"type:varchar(100);unique_index"//type设置sql类型，unique_index为该列设置唯一索引`

  Address string `gorm:"not null;unique"` //非空

  no string `gorm:"index:idx_no"` // 创建索引并命名，如果有其他同名索引，则创建组合索引

  code string `gorm:"index:idx_no"

  `

  Profile Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"` //设置外键

  ProfileID int

  remark string `gorm:"default:'test'"` //默认值

}

func main() {

	psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)

	db, err := gorm.Open("postgres", psqlInfo)

	if err != nil{

		panic(err)

	}

	defer db.Close()

	// 全局禁用表名复数

    db.SingularTable(true)

    db.AutoMigrate(&User{}, &Profile{})

}

3)自定义表名

// 自定义表名(方法一)

func (User) TableName() string{

    return "my_user"

}

...

// 自定义表名(方法二)

db.Table("my_user").CreateTable(&User{})

8.创建

user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}

db.NewRecord(user) // => 主键为空返回`true`

db.Create(&user)

db.NewRecord(user) // => 创建`user`后返回`false`

9.查询

// 获取第一条记录，按主键排序

db.First(&user)

//// SELECT * FROM users ORDER BY id 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;

1)Where查询条件(简单SQL)

// 获取第一个匹配记录

db.Where("name = ?", "jinzhu").First(&user)

//// SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// 获取所有匹配记录

db.Where("name = ?", "jinzhu").Find(&users)

//// SELECT * FROM users WHERE name = 'jinzhu';

db.Where("name <> ?", "jinzhu").Find(&users)

// IN

db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)

// LIKE

db.Where("name LIKE ?", "%jin%").Find(&users)

// AND

db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)

// Time

db.Where("updated_at > ?", lastWeek).Find(&users)

db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)

2)Where查询条件（Struct & 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;

// 主键的Slice

db.Where([]int64{20, 21, 22}).Find(&users)

//// SELECT * FROM users WHERE id IN (20, 21, 22);

3)Not条件

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";

4)带内联条件的查询

// 按主键获取

db.First(&user, 23)

//// SELECT * FROM users WHERE id = 23 LIMIT 1;

// 简单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;

5)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)

6)查询链

db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users)

//// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin';

db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)

7)FirstOrInit

获取第一个匹配的记录，若没有，则根据条件初始化一个新的记录

// Unfound

db.FirstOrInit(&user, User{Name: "non_existing"})

//// user -> User{Name: "non_existing"}

// Found

db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user)

//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"})

//// user -> User{Id: 111, Name: "Jinzhu", Age: 20}

8)Attrs

如果未找到记录，则使用参数初始化结构

// Unfound

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}

// Found

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}

9)Assign

将参数分配给结果，不管是否找到

// Unfound

db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user)

//// user -> User{Name: "non_existing", Age: 20}

// Found

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}

10)FirstOrCreate

获取第一个匹配的结果，或创建一个具有给定条件的新纪录

// Unfound

db.FirstOrCreate(&user, User{Name: "non_existing"})

//// INSERT INTO "users" (name) VALUES ("non_existing");

//// user -> User{Id: 112, Name: "non_existing"}

// Found

db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user)

//// user -> User{Id: 111, Name: "Jinzhu"}

11)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;

12)Order

从数据库检索记录时指定顺序

db.Order("age desc, name").Find(&users)

//// SELECT * FROM users ORDER BY age desc, name;

// Multiple orders

db.Order("age desc").Order("name").Find(&users)

//// SELECT * FROM users ORDER BY age desc, name;

// ReOrder

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)

13)Limit

指定要检索的记录数

db.Limit(3).Find(&users)

//// SELECT * FROM users LIMIT 3;

// Cancel limit condition with -1

db.Limit(10).Find(&users1).Limit(-1).Find(&users2)

//// SELECT * FROM users LIMIT 10; (users1)

//// SELECT * FROM users; (users2)

14)Offset

指定在开始返回记录之前要跳过的记录数

db.Offset(3).Find(&users)

//// SELECT * FROM users OFFSET 3;

// Cancel offset condition with -1

db.Offset(10).Find(&users1).Offset(-1).Find(&users2)

//// SELECT * FROM users OFFSET 10; (users1)

//// SELECT * FROM users; (users2)

15)Count

获取模型的记录数

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;

16)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() {

    ...

}

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)

17)Join

指定连接条件

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)

18)Pluck

查询单列，返回数组

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)

19)Scan

将结果扫描到另一个结构中

type Result struct {

    Name string

    Age  int

}

var result Result

db.Table("users").Select("name, age").Where("name = ?", 3).Scan(&result)

// Raw SQL

db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

20)预加载

数据库编译一次，然后保存，提高执行效率

db.Preload("Orders").Find(&users)

10.更新

1)更新全部字段 Save

db.First(&user)

user.Name = "jinzhu 2"

user.Age = 100

db.Save(&user)

//// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;

2)更新更改字段 Update Updates

// 更新单个属性（如果更改）

db.Model(&user).Update("name", "hello")

//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// 使用组合条件更新单个属性

db.Model(&user).Where("active = ?", true).Update("name", "hello")

//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;

// 使用`map`更新多个属性，只会更新这些更改的字段

db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

//// UPDATE users SET name='hello', age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

// 使用`struct`更新多个属性，只会更新这些更改的和非空白字段

db.Model(&user).Updates(User{Name: "hello", Age: 18})

//// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// 警告:当使用struct更新时，FORM将仅更新具有非空值的字段

// 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值

db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

3)更新选择字段

db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

//// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "actived": false})

//// UPDATE users SET age=18, actived=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

4)使用sql表达式更新

DB.Model(&product).Update("price", gorm.Expr("price * ? + ?", 2, 100))

//// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';

DB.Model(&product).Updates(map[string]interface{}{"price": gorm.Expr("price * ? + ?", 2, 100)})

//// UPDATE "products" SET "price" = price * '2' + '100', "updated_at" = '2013-11-17 21:34:10' WHERE "id" = '2';

DB.Model(&product).UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))

//// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2';

DB.Model(&product).Where("quantity > 1").UpdateColumn("quantity", gorm.Expr("quantity - ?", 1))

//// UPDATE "products" SET "quantity" = quantity - 1 WHERE "id" = '2' AND quantity > 1;

11.删除

1)删除

// 删除存在的记录

db.Delete(&email)

//// DELETE from emails where id=10;

2)批量删除

db.Where("email LIKE ?", "%jinzhu%").Delete(Email{})

//// DELETE from emails where email LIKE "%jinhu%";

db.Delete(Email{}, "email LIKE ?", "%jinzhu%")

//// DELETE from emails where email LIKE "%jinhu%";

3)软删除

如果模型有DeletedAt字段，删除时是软删除

db.Delete(&user)

//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 批量删除

db.Where("age = ?", 20).Delete(&User{})

//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;

// 软删除的记录将在查询时被忽略

db.Where("age = 20").Find(&user)

//// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

// 使用Unscoped查找软删除的记录

db.Unscoped().Where("age = 20").Find(&users)

//// SELECT * FROM users WHERE age = 20;

// 使用Unscoped永久删除记录

db.Unscoped().Delete(&order)

//// DELETE FROM orders WHERE id=10;

12.事务

func CreateAnimals(db *gorm.DB) err {

  tx := db.Begin()

  // 注意，一旦你在一个事务中，使用tx作为数据库句柄

  if err := tx.Create(&Animal{Name: "Giraffe"}).Error; err != nil {

     tx.Rollback()

     return err

  }

  if err := tx.Create(&Animal{Name: "Lion"}).Error; err != nil {

     tx.Rollback()

     return err

  }

  tx.Commit()

  return nil

}

13.SQL构建

1)执行原生sql

db.Exec("DROP TABLE users;")

db.Exec("UPDATE orders SET shipped_at=? WHERE id IN (?)", time.Now, []int64{11,22,33})

// Scan

type Result struct {

    Name string

    Age  int

}

var result Result

db.Raw("SELECT name, age FROM users WHERE name = ?", 3).Scan(&result)

2)sql.Row & sql.Rows

row := db.Table("users").Where("name = ?", "jinzhu").Select("name, age").Row() // (*sql.Row)

row.Scan(&name, &age)

rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error)

defer rows.Close()

for rows.Next() {

    ...

    rows.Scan(&name, &age, &email)

    ...

}

// Raw SQL

rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows() // (*sql.Rows, error)

defer rows.Close()

for rows.Next() {

    ...

    rows.Scan(&name, &age, &email)

    ...

}

3)迭代中使用sql.Rows的Scan

rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error)

defer rows.Close()

for rows.Next() {

  var user User

  db.ScanRows(rows, &user)

  // do something

}

14.日志

// 启用Logger，显示详细日志

db.LogMode(true)

// 禁用日志记录器，不显示任何日志

db.LogMode(false)

// 调试单个操作，显示此操作的详细日志

db.Debug().Where("name = ?", "jinzhu").First(&User{})

15.架构

Gorm使用可链接的API, *gorm.DB是链的桥梁

db, err := gorm.Open("postgres", "user=gorm dbname=gorm sslmode=disable")

// 创建新关系

db = db.Where("name = ?", "jinzhu")

// 过滤更多

if SomeCondition {

    db = db.Where("age = ?", 20)

} else {

    db = db.Where("age = ?", 30)

}

if YetAnotherCondition {

    db = db.Where("active = ?", 1)

}

巴特西

Gorm使用详解

最新文章

热门文章