概述

package main

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

type Product struct {
	ID uint `gorm:"primary_key"`
	Code string
	Price uint
}

func main() {
	db,err := gorm.Open("MysqL","user:password@/dbname?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		panic("Failed to connect database")
	}
	defer db.Close()

	// 自动迁移表，生成的表名为 products
	db.AutoMigrate(&Product{})

	// Create
	db.Create(&Product{Code: "L1212",Price: 1000})

	// Read
	var product Product
	db.First(&product,1) // find product with id 1
	db.First(&product,"code = ?","L1212") // find product with code l1212

	// Update
	db.Model(&product).Update("Price",2000)

	 //Delete
	db.Delete(&product)

}

模型定义

4 个特殊字段。其中 ID 字段默认为主键字段，可以无需加 Tag `gorm:"primary_key"`。

type Model struct { 
  ID        uint `gorm:"primary_key"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt *time.Time
}

使用 gorm.Model 自动生成这4个字段。效果等同上。

type User struct {
    gorm.Model
}

修改表名

type Product struct {
	ID    uint
	Code  string
	Price uint
}

//修改默认表名
func (Product) TableName() string {
	return "product2"
}

type Email struct {
	ID         int
	Email      string
}

func main() {
	db,"root:root@tcp(localhost:3306)/gorm")
	if err != nil {
		panic("Failed to connect database")
	}
	defer db.Close()

	//设置默认表名前缀
	gorm.DefaultTableNameHandler = func(db *gorm.DB,defaultTableName string) string {
		return "prefix_" + defaultTableName
	}

	//自动生成表
	db.AutoMigrate(&Product{},&Email{})

}

设置字段

type Product struct {
	ID    uint `gorm:"primary_key:id"`
	Num   int  `gorm:"AUTO_INCREMENT:number"`
	Code  string
	Price uint  `gorm:"default:'1000'"`
	Tag   []Tag     `gorm:"many2many:tag;"`
	Date  time.Time `gorm:"-"`
}

type Email struct {
	ID         int    `gorm:"primary_key:id"`
	UserID     int    `gorm:"not null;index"`
	Email      string `gorm:"type:varchar(100);unique_index"`
	Subscribed bool
}

type Tag struct {
	Name string
}

func main() {
	db,"root:root@tcp(localhost:3306)/gorm")
	if err != nil {
		panic("Failed to connect database")
	}
	defer db.Close()

	gorm.DefaultTableNameHandler = func(db *gorm.DB,defaultTableName string) string {
		return "demo_" + defaultTableName
	}

	db.AutoMigrate(&Product{},&Email{})

}

设置外键字段

type Profile struct {
	gorm.Model
	Refer int
	Name  string
}

type User struct {
	gorm.Model
	Profile   Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"`
	ProfileID int
}

增删改查

增

type Animal struct {
    ID   int64
    Name string `gorm:"default:'galeone'"`
    Age  int64
}

var animal = Animal{Age: 99,Name: ""}
db.Create(&animal)

查

// SELECT * FROM users ORDER BY id LIMIT 1;
db.First(&user)

// SELECT * FROM users ORDER BY id DESC LIMIT 1;
db.Last(&user)

// SELECT * FROM users;
db.Find(&users)

// SELECT * FROM users WHERE id = 10;
db.First(&user,10)

添加 where子句

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

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

结构或者 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 of primary keys
db.Where([]int64{20,21,22}).Find(&users)
////  SELECT * FROM users WHERE id IN (20,22);

inline condition

// Get by primary key (only works for integer primary key)
db.First(&user,23)
//// SELECT * FROM users WHERE id = 23 LIMIT 1;
// Get by primary key if it were a non-integer type
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 > ?",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;

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;

排序

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)

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)

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;

group & having

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

db.Table("users").Select("users.name,emails.email").Joins("left join emails on emails.user_id = users.id").Scan(&results)

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)

指定表名

// Create `deleted_users` table with struct User's definition
db.Table("deleted_users").CreateTable(&User{})

改

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;

部分更新

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

对更新语句进行 select 和 omit

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","actived": false})
//// UPDATE users SET age=18,actived=false,updated_at='2013-11-17 21:34:10' WHERE id=111;

删

// Delete an existing record
db.Delete(&email)
//// DELETE from emails where id=10;

软删除

当设置DeletedAt 字段时，默认不会真的删除该记录。只会把该记录的 DeletedAt 的值设置为当前时间。

// 执行软删除
db.Delete(&user)
//// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;

// 虽然记录没有永久删除。但是查询时依然不会查询到该记录
db.Where("age = 20").Find(&user)
//// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;

// 可以通过指定域查询到该软删除的记录
db.Unscoped().Where("age = 20").Find(&users)
//// SELECT * FROM users WHERE age = 20;

// 通过指定域进行永久删除
db.Unscoped().Delete(&order)
//// DELETE FROM orders WHERE id=10;

子查询

db.Preload("Orders").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,2,3,4);

db.Preload("Orders","state NOT IN (?)","cancelled").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,4) AND state NOT IN ('cancelled');

db.Where("state = ?","active").Preload("Orders","cancelled").Find(&users)
//// SELECT * FROM users WHERE state = 'active';
//// SELECT * FROM orders WHERE user_id IN (1,2) AND state NOT IN ('cancelled');

db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users)
//// SELECT * FROM users;
//// SELECT * FROM orders WHERE user_id IN (1,4); // has many
//// SELECT * FROM profiles WHERE user_id IN (1,4); // has one
//// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to

db.Preload("Orders.OrderItems").Find(&users)
db.Preload("Orders","state = ?","paid").Preload("Orders.OrderItems").Find(&users)

关联存储

增和改时默认级联处理

user := User{
    Name:            "jinzhu",BillingAddress:  Address{Address1: "Billing Address - Address 1"},ShippingAddress: Address{Address1: "Shipping Address - Address 1"},Emails:          []Email{
                                        {Email: "jinzhu@example.com"},{Email: "jinzhu-2@example@example.com"},},Languages:       []Language{
                     {Name: "ZH"},{Name: "EN"},}

db.Create(&user)
//// BEGIN TRANSACTION;
//// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1");
//// INSERT INTO "addresses" (address1) VALUES ("Shipping Address - Address 1");
//// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu",1,2);
//// INSERT INTO "emails" (user_id,email) VALUES (111,"jinzhu@example.com");
//// INSERT INTO "emails" (user_id,"jinzhu-2@example.com");
//// INSERT INTO "languages" ("name") VALUES ('ZH');
//// INSERT INTO user_languages ("user_id","language_id") VALUES (111,1);
//// INSERT INTO "languages" ("name") VALUES ('EN');
//// INSERT INTO user_languages ("user_id",2);
//// COMMIT;

db.Save(&user)

取消默认关联存储

type User struct {
  gorm.Model
  Name      string
  CompanyID uint
  Company   Company `gorm:"save_associations:false"`
}

type Company struct {
  gorm.Model
  Name string
}

或着手动取消

db.Set("gorm:save_associations",false).Create(&user)

db.Set("gorm:save_associations",false).Save(&user)

表间关系

一对多关系

type User struct {
    gorm.Model
    Emails   []Email
}

type Email struct {
    gorm.Model
    Email   string
    UserID  uint
}

// 查询某userid为111的用户的所有Email 地址
db.Model(&user).Related(&emails)
//// SELECT * FROM emails WHERE user_id = 111; // 111 is user's primary key

多对多关系

(相互关联）

type User struct {
    gorm.Model
    Languages         []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
    gorm.Model
    Name string
    Users               []User     `gorm:"many2many:user_languages;"`
}

// 查询某语言为111的所有用户
db.Model(&language).Related(&users)
//// SELECT * FROM "users" INNER JOIN "user_languages" ON "user_languages"."user_id" = "users"."id" WHERE  ("user_languages"."language_id" IN ('111'))

（单一关联，比如个人与同学）

type User struct {
    gorm.Model
    Languages         []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
    gorm.Model
    Name string
}

db.Model(&user).Related(&languages,"Languages")
//// SELECT * FROM "languages" INNER JOIN "user_languages" ON "user_languages"."language_id" = "languages"."id" WHERE "user_languages"."user_id" = 111

关联模式

（方便处理处理多对多）

// 开始关联
db.Model(&user).Association("Languages")


// 查询
db.Model(&user).Association("Languages").Find(&languages)


// 添加
db.Model(&user).Association("Languages").Append([]Language{languageZH,languageEN})
db.Model(&user).Association("Languages").Append(Language{Name: "DE"})


// 删除
db.Model(&user).Association("Languages").Delete([]Language{languageZH,languageEN})
db.Model(&user).Association("Languages").Delete(languageZH,languageEN)


// 更新
db.Model(&user).Association("Languages").Replace([]Language{languageZH,languageEN})
db.Model(&user).Association("Languages").Replace(Language{Name: "DE"},languageEN)


db.Model(&user).Association("Languages").Count()


// 移除关联
db.Model(&user).Association("Languages").Clear()

高级用法

事务

func CreateAnimals(db *gorm.DB) err {
  tx := db.Begin()
  // Note the use of tx as the database handle once you are within a transaction

  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
}

原生 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,3).Scan(&result)

sql.DB 接口

// Get generic database object `*sql.DB` to use its functions
db.DB()

// Ping
db.DB().Ping()

db.DB().SetMaxIdleConns(10)
db.DB().SetMaxOpenConns(100)

日志处理

// Enable Logger,show detailed log
db.LogMode(true)

// Diable Logger,don't show any log
db.LogMode(false)

// Debug a single operation,show detailed log for this operation
db.Debug().Where("name = ?","jinzhu").First(&User{})

//默认 error，设置日志级别
db.SetLogger(gorm.Logger{revel.TRACE})

错误处理

// 一般处理
if err := db.Where("name = ?","jinzhu").First(&user).Error; err != nil {
    // error handling...
}

// 获取所有的错误
db.First(&user).Limit(10).Find(&users).GetErrors()

// 获取记录找不到错误（不排除有其他错误）
db.Where("name = ?","hello world").First(&user).RecordNotFound()

函数钩子

阅读原文

原文链接：https://www.f2er.com/go/188464.html

golang - gorm

概述