gorm模型

数据库连接

在libs/mysql/mysql.go中连接数据库

package mysql

import (
    "fmt"
    "project/config"

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

var Db *gorm.DB

func init() {
    var (
        hostname, database, username, password, prefix string
    )

    hostname = config.MysqlHostName
    database = config.MysqlDb
    username = config.MysqlUser
    password = config.MysqlPassWord
    prefix   = config.MysqlPrefix

    db, err := gorm.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s?parseTime=True&loc=Local",
        username, password, hostname, database))
    if err != nil {
        fmt.Printf("mysql connect error %s", err)
        return
    }

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

    db.LogMode(true) // 打印SQL语句
    db.SingularTable(true)
    db.DB().SetMaxIdleConns(10)
    db.DB().SetMaxOpenConns(100)

    Db = db

    fmt.Printf("mysql connect success.")
}

CURD操作

package model

import (
    "project/libs/mysql"
)

type User struct {
    Id                int            `json:"id"`
    RegIp            string        `json:"reg_ip"`
    Name            int            `json:"name"`
    Age             uint8       `json:"age"`
    CreateTime        int64        `json:"create_time"`
}

创建

user := User{Name: "Jinzhu", RegIp: '127.0.0.1', CreateTime: time.Now().Unix()}

mysql.Db.Create(&user)

修改

user := User{Id: 1}

// 使用主键更新单个字段
mysql.Db.Model(&user).Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1;

// 根据条件更新字段
mysql.Db.Model(&user).Where("reg_ip = ?", '127.0.0.1').Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1 AND reg_ip='127.0.0.1';

更新多个字段
mysql.Db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18})
// UPDATE go_user SET name='hello', age=18 WHERE id=1;

// 使用表名更新,需要使用表全名
mysql.Db.Table("go_user").Where("reg_ip = ?", '127.0.0.1').Update("name", "hello")
// UPDATE go_user SET name='hello' WHERE id=1 AND reg_ip='127.0.0.1';


// 使用`struct`更新多个属性，只会更新这些更改的和非空白字段
mysql.Db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE go_user SET name='hello', age=18 WHERE id = 1;

// 警告:当使用struct更新时，FORM将仅更新具有非空值的字段
// 对于下面的更新，什么都不会更新为""，0，false是其类型的空白值
mysql.Db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

删除

user := User{Id: 1}

mysql.Db.Delete(&user)
// DELETE from go_user where id=1;

mysql.Db.Delete(User{}, "name = ?", "hello")
// DELETE from go_user where name='hello';

查询

user := User{}
users := []*User

// 获取第一条记录，按主键排序
mysql.Db.First(&user)
// SELECT * FROM go_user ORDER BY id LIMIT 1;

// 获取最后一条记录，按主键排序
mysql.Db.Last(&user)
// SELECT * FROM go_user ORDER BY id DESC LIMIT 1;

// 获取所有记录

mysql.Db.Find(&users)
// SELECT * FROM go_user;

// 使用主键获取记录
mysql.Db.First(&user, 10)
// SELECT * FROM go_user WHERE id = 10;

Where

// 获取第一个匹配记录
mysql.Db.Where("name = ?", "jinzhu").First(&user)
//// SELECT * FROM user WHERE name = 'jinzhu' limit 1;

// 获取所有匹配记录
mysql.Db.Where("name = ?", "jinzhu").Find(&users)
//// SELECT * FROM user WHERE name = 'jinzhu';

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

// IN
mysql.Db.Where("name in (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)

// LIKE
mysql.Db.Where("name LIKE ?", "%jin%").Find(&users)

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

// Time
mysql.Db.Where("updated_at > ?", lastWeek).Find(&users)

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


// Struct
// 注意：当使用struct查询时，GORM将只查询那些具有值的字段
mysql.Db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM user WHERE name = "jinzhu" AND age = 20 LIMIT 1;

// Map
mysql.Db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM user WHERE name = "jinzhu" AND age = 20;

// 主键的Slice
mysql.Db.Where([]int64{20, 21, 22}).Find(&users)
// SELECT * FROM user WHERE id IN (20, 21, 22);

// 查询链
mysql.Db.Where("name <> ?","hello").Where("age >= ? ",20).Find(&users)

Or

mysql.Db.Where("name = ?", "admin").Or("age = ?", 18).Find(&users)
//// SELECT * FROM user WHERE name = 'admin' OR age = 18;

// Struct
mysql.Db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users)
//// SELECT * FROM user WHERE name = 'jinzhu' OR name = 'jinzhu 2';

// Map
mysql.Db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)

Select

mysql.Db.Select("name, age").Find(&users)
// SELECT name, age FROM user;

mysql.Db.Select([]string{"name", "age"}).Find(&users)
// SELECT name, age FROM user;

Order

mysql.Db.Order("age desc, name").Find(&users)
// SELECT * FROM user ORDER BY age desc, name;

// Multiple orders
mysql.Db.Order("age desc").Order("name").Find(&users)
// SELECT * FROM user ORDER BY age desc, name;

Limit

mysql.Db.Limit(3).Find(&users)
// SELECT * FROM user LIMIT 3;

Offset

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

mysql.Db.Offset(3).Find(&users)
// SELECT * FROM user OFFSET 3;

Count

var count int
mysql.Db.Model(&User{}).Where("name = ?", "hello").Count(&count)
// SELECT count(*) FROM user WHERE name = 'hello';

Group

type NameCount struct{
    Name         string        `json:"name"`
    Total        int            `json:"total"`
}

var list []*NameCount
mysql.Db.Table("go_user").Select("name, count(name) as total").Group("name").Scan(&list)

Joins

type Result struct{
    Name    string      `json:"name"`
    Email   string      `json:"email"`
}

mysql.Db.Table("go_user as u").Select("u.name, e.email").Joins("left join go_email as e on e.user_id = u.id").Scan(&results)

Pluck

var ages []int64
mysql.Db.Find(&users).Pluck("age", &ages)

var names []string
mysql.Db.Model(&User{}).Pluck("name", &names)

错误处理

if err := mysql.Db.Where("name = ?", "hello").First(&user).Error; err != nil {
    // 错误处理...
}

事务

// 开始事务
tx := mysql.Db.Begin()

// 在事务中做一些数据库操作
tx.Create(...)

// ...

// 发生错误时回滚事务
tx.Rollback()

// 或提交事务
tx.Commit()