Gorm教程

OrangBus大约 8 分钟

basemodel.go

package models

import (
	"database/sql/driver"
	"fmt"
	"github.com/gin-gonic/gin"
	"github.com/spf13/cast"
	"gorm.io/gorm"
	"time"
)

type TableId struct {
	ID uint `gorm:"primaryKey;autoIncrement:true" json:"id"`
}
type TableTime struct {
	CreatedAt Time `gorm:"column:created_at;index;" json:"created_at,omitempty"`
	UpdatedAt Time `gorm:"column:updated_at;index;" json:"updated_at,omitempty"` // https://github.com/go-gorm/datatypes
}

const timeFormat = "2006-01-02 15:04:05"
const timezone = "Asia/Shanghai"

type Time time.Time

func (t Time) MarshalJSON() ([]byte, error) {
	b := make([]byte, 0, len(timeFormat)+2)
	b = append(b, '"')
	b = time.Time(t).AppendFormat(b, timeFormat)
	b = append(b, '"')
	return b, nil
}
func (t *Time) UnmarshalJSON(data []byte) (err error) {
	now, err := time.ParseInLocation(`"`+timeFormat+`"`, string(data), time.Local)
	*t = Time(now)
	return
}

func (t Time) String() string {
	return time.Time(t).Format(timeFormat)
}

func (t Time) local() time.Time {
	loc, _ := time.LoadLocation(timezone)
	return time.Time(t).In(loc)
}

func (t Time) Value() (driver.Value, error) {
	var zeroTime time.Time
	var ti = time.Time(t)
	if ti.UnixNano() == zeroTime.UnixNano() {
		return nil, nil
	}
	return ti, nil
}

func (t *Time) Scan(v interface{}) error {
	value, ok := v.(time.Time)
	if ok {
		*t = Time(value)
		return nil
	}
	return fmt.Errorf("can not convert %v to timestamp", v)
}

// 分页查询： db.Scopes(models.Paginate(c))
func Paginate(r *gin.Context) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		page := cast.ToInt(r.Query("page"))
		limit := cast.ToInt(r.Query("limit"))
		if page <= 0 {
			page = 1
		}
		if limit <= 0 || limit > 100 {
			limit = 10
		}
		return db.Offset((page - 1) * limit).Limit(limit).Order("id desc")
	}
}

安装

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql

viper-读取配置文件

https://github.com/spf13/viper

go get github.com/spf13/viper

初始化

常用表标签

baseModel

package models

import (
	"gorm.io/gorm"
	"time"
)

type LocalTime time.Time

// 格式化时间
func (t *LocalTime) MarshalJSON() ([]byte, error) {
	formatted := time.Time(*t).Format("2006-01-02 15:04:05")
	return []byte(formatted), nil
}

type BaseModelId struct {
	ID uint `gorm:"primarykey"`
}

type BaseModelTime struct {
	CreatedAt time.Time `gorm:"column:created_at;index;" json:"created_at,omitempty"`
	UpdatedAt time.Time `gorm:"column:updated_at;index;" json:"updated_at,omitempty"`
}

type BaseModel struct {
	gorm.Model
	CreatedAt time.Time `gorm:"column:created_at;index;" json:"created_at,omitempty"`
	UpdatedAt time.Time `gorm:"column:updated_at;index;" json:"updated_at,omitempty"`

	//CreatedAt *LocalTime `json:"created_at"`
	//UpdatedAt *LocalTime `json:"updated_at"`
	//DeletedAt *LocalTime `json:"updated_at" gorm:"index"`
}

package models

import "time"

type Website struct {
	BaseModelId
	Name      string    `json:"name" gorm:"type:varchar(50);not null; default='聚合影视';comment:网站名称"`
	Url       string    `json:"url" gorm:"type:varchar(50);not null; comment:网站地址"`
	Email     string    `json:"email" gorm:"type:varchar(50);comment:网站邮箱"`
	Logo      string    `json:"logo" gorm:"type:varchar(50);not null;size:50;comment:网站logo"`
	Keywords  string    `json:"keywords" gorm:"type:varchar(255);comment:网站关键词"`
	Desc      string    `json:"desc" gorm:"type:varchar(255);comment:网站描述"`
	CreatedAt time.Time `json:"created_at"`
	BaseModelTime
}

func (a *MapData) Comment() string {
	return "ALTER TABLE Websites COMMENT='网站设置';"
}

表迁移

func MigrateTable() {
	M := db.Migrator()
	if !M.HasTable(&MovieCate{}) {
		M.CreateTable(&MovieCate{})
	}
}

// 或者是
db.AutoMigrate(&MovieCate{})

时间处理

package models

import (
	"fmt"
	"time"
)

type LocalTime time.Time

func (t *LocalTime) MarshalJSON() ([]byte, error) {
	tTime := time.Time(*t)
	return []byte(fmt.Sprintf("\"%v\"", tTime.Format("2006-01-02 15:04:05"))), nil
}

type BaseModel struct {
	ID        uint      `gorm:"primarykey" json:"id"`
	CreatedAt LocalTime `json:"created_at"`
	UpdatedAt LocalTime `json:"updated_at"`
}

时间赋值

item.CreatedAt = models.Time(time.Now())

自定义表名

package models

import (
	"github.com/google/uuid"
	"gorm.io/gorm"
)

type User struct {
	BaseModel
	UUid     string `json:"uuid" gorm:"unique"`
	Name     string `json:"name" `
	Phone    string `json:"phone" `
	Password string `json:"password" `
	MerId    int64  `json:"mer_id"`
}

func (User) TableName() string {
	return "user"
}

// 表备注

baseModdel

package models

import (
	"database/sql/driver"
	"fmt"
	"time"
)

type TableId struct {
	ID int64 `gorm:"primaryKey;autoIncrement:true" json:"id"`
}
type TableTime struct {
	CreatedAt Time `gorm:"column:created_at;index;" json:"created_at,omitempty"`
	UpdatedAt Time `gorm:"column:updated_at;index;" json:"updated_at,omitempty"` // https://github.com/go-gorm/datatypes
}

const timeFormat = "2006-01-02 15:04:05"
const timezone = "Asia/Shanghai"

type Time time.Time

func (t Time) MarshalJSON() ([]byte, error) {
	b := make([]byte, 0, len(timeFormat)+2)
	b = append(b, '"')
	b = time.Time(t).AppendFormat(b, timeFormat)
	b = append(b, '"')
	return b, nil
}

func (t *Time) UnmarshalJSON(data []byte) (err error) {
	now, err := time.ParseInLocation(`"`+timeFormat+`"`, string(data), time.Local)
	*t = Time(now)
	return
}

func (t Time) String() string {
	return time.Time(t).Format(timeFormat)
}

func (t Time) local() time.Time {
	loc, _ := time.LoadLocation(timezone)
	return time.Time(t).In(loc)
}

func (t Time) Value() (driver.Value, error) {
	var zeroTime time.Time
	var ti = time.Time(t)
	if ti.UnixNano() == zeroTime.UnixNano() {
		return nil, nil
	}
	return ti, nil
}

func (t *Time) Scan(v interface{}) error {
	value, ok := v.(time.Time)
	if ok {
		*t = Time(value)
		return nil
	}
	return fmt.Errorf("can not convert %v to timestamp", v)
}

自动生成表

1、使用laravel建表

创建一个laravel项目，通过laravel的迁移创建表。（一定要这样吗？不）

2、使用 gen tool 自动生成model文件

go install gorm.io/gen/tools/gentool@latest

创建一个 migrate.sh 文件，（死的东西，复制改改配置）

#!/usr/bin/env bash

# 使用方法：
# ./migrate.sh
# 再将./models下的文件剪切到对应服务的model目录里面，记得改package

#生成的表名
tables=users,admins
#包名
modelPkgName=models
#表生成的genmodel目录
outPath="./app/models"
# 数据库配置
host="home.cc"
port=3306
dbname=cloud_movie
username=cloud_movie
passwd=admin666


echo "开始创建库：$dbname"
gentool -dsn "${username}:${passwd}@tcp(${host}:${port})/${dbname}?charset=utf8mb4&parseTime=True&loc=Local" \
        -tables "${tables}" \
        -onlyModel=true \
        -modelPkgName="${modelPkgName}" \
        -outPath="${outPath}" \
        -fieldWithTypeTag=true \
        -fieldWithIndexTag=true \

查询

group by

SELECT created_at,SUM(count) as total from map_origin WHERE cate_id = 1 and created_at >= '2024-09-09 07:15:01' GROUP BY created_at;

package models

import (
	"gin-web/pkg/database"
	"github.com/golang-module/carbon/v2"
)

// 原始数据
type MapOrigin struct {
	TableId
	CateId int64  `gorm:"column:cate_id;size:100;default:0;comment:分类id" json:"cate_id"`
	Name   string `gorm:"column:name;size:100;comment:名称" json:"name"`
	Point  string `gorm:"column:point;size:255;default:0;comment:坐标" json:"point"`
	Count  int64  `gorm:"column:count;size:50;default:0;comment:数量" json:"count"`
	Batch  int    `gorm:"column:batch;size:50;default:0;comment:批次" json:"batch"`
	TableTime
}

func (m *MapOrigin) TableName() string {
	return "map_origin"
}
func (a *MapOrigin) Comment() string { return "ALTER TABLE map_origin COMMENT='原始数据表';" }

type DataList struct {
	CreatedAt string `json:"created_at"`
	Total     int64  `json:"total"`
}

func GetList(cate_id int) ([]DataList, error) {
	date := carbon.Now()
	reslist := []DataList{}
	if err := database.DB.Model(MapOrigin{}).Select("created_at,SUM(count) as total").Where("cate_id =? ", cate_id).Where("created_at >= ?", date.SubHours(2).ToDateTimeString()).Group("created_at").Scan(&reslist).Error; err != nil {
		return reslist, err
	}
	list := []DataList{}
	for _, row := range reslist {
		list = append(list, DataList{
			CreatedAt: carbon.Parse(row.CreatedAt).ToTimeString(),
			Total:     row.Total,
		})
	}
	return list, nil
}

分页查询

// 分页查询： db.Scopes(models.Paginate(c))
func Paginate(r *gin.Context) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		page := cast.ToInt(r.Query("page"))
		limit := cast.ToInt(r.Query("limit"))
		if page <= 0 {
			page = 1
		}
		if limit <= 0 || limit > 100 {
			limit = 10
		}
		return db.Offset((page - 1) * limit).Limit(limit).Order("id desc")
	}
}

使用

var userList models.Users
database.DB.Scopes(models.Paginate(c)).find(&userList)

添加

删除

一对一

一个用户对应一个订单表

package main

import (
	"fmt"
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

type User struct {
	gorm.Model
	Username string
	Order    Order `gorm:"foreignKey:Uid"`
}

type Order struct {
	gorm.Model
	Uid   uint64
	Price float64
}

var DB *gorm.DB

func Content() {
	db, err := gorm.Open(sqlite.Open("database.db"), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}
	DB = db
}

func main() {
	Content()
	DB.AutoMigrate(&User{})
	DB.AutoMigrate(&Order{})

	var user User
	DB.Preload("Order").Find(&user, 1) // 用户表关联 订单表 查询
	fmt.Println("user", user)
	fmt.Println("order", user.Order)
}

一对多

一个用户对应多个订单

package main

import (
	"fmt"
	"github.com/glebarez/sqlite"
	"gorm.io/gorm"
)

// 主表
type User struct {
	gorm.Model
	Username string
	Orders   []Order `gorm:"foreignKey:Uid"` // 这里是一个分片
}

type Order struct {
	gorm.Model
	Uid   uint64
	Price float64
    User User `gorm:"foreignKey:Uid"` // 指定当前表的外键即可
}

var DB *gorm.DB

func Content() {
	db, err := gorm.Open(sqlite.Open("database.db"), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}
	DB = db
}

func main() {
	Content()
	DB.AutoMigrate(&User{})
	DB.AutoMigrate(&Order{})

	var user User
	DB.Preload("Orders").Find(&user, 1)
	fmt.Println("users", user)
	if len(user.Orders) > 0 {
		for _, item := range user.Orders {
			fmt.Println("order", item)
		}
	}
    
    // 查询订单列表的时候携带创建订单的用户
    
}

func GetListWithUser(page, limit int) (orderList []models.Order) {
	order := []models.Order{}
	database.DB.Preload("User").Limit(limit).Offset(page).Order("id desc").Find(&order)
	return order
}

多对多

案例

package main

import (
	"database/sql"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"time"
)

type MovieCate struct {
	Id        uint32    `json:"id"`
	ApiId     int       `json:"api_id"`
	TypeName  string    `json:"name"`
	TypeId    int       `json:"type_id"`
	Status    int       `json:"status"`
	Sort      int       `json:"sort"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

var drive = "mysql"
var database = "golang"
var host = "127.0.0.1"
var port = 3306
var username = "root"
var password = "root"

// 获取当前时间
func getTime() string {
	return time.Now().Format("2006-01-02 15:04:05")
}

// 全局数据库对象
var db = &gorm.DB{}

func init() {

	// 连接数据库
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local", username, password, host, port, database)
	mysqlDb, err := sql.Open(drive, dsn)
	if err != nil {
		panic(err)
	}
	db, err = gorm.Open(mysql.New(mysql.Config{
		Conn: mysqlDb,
	}), &gorm.Config{
		// 日志配置
		Logger: logger.New(
			log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
			logger.Config{
				SlowThreshold:             time.Second, // 慢 SQL 阈值
				LogLevel:                  logger.Info, // 日志级别
				IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
				Colorful:                  false,       // 禁用彩色打印
			}),
	})
	if err != nil {
		panic("数据库连接失败")
	}
	fmt.Println("数据库连接成功！")
}

// 插入数据
func Create() {
	cate := MovieCate{
		ApiId:     1,
		TypeId:    1,
		TypeName:  "demo",
		Status:    1,
		Sort:      10,
		CreatedAt: time.Now(),
		UpdatedAt: time.Now(),
	}
	result := db.Create(&cate)
	fmt.Println(result)
}

// 批量插入
func BatchInsert() {
	var cateList = []MovieCate{}
	for i := 0; i < 10; i++ {
		cateList = append(cateList, MovieCate{
			ApiId:     i + 1,
			TypeId:    i + 1,
			TypeName:  "demo",
			Status:    1,
			Sort:      i,
			CreatedAt: time.Now(),
			UpdatedAt: time.Now(),
		})
	}
	res := db.Create(&cateList)
	fmt.Println(res)
}

// 查询数据
func getDataById(id int) {
	//cate := new(MovieCate)
	//db.First(cate)
	//fmt.Println(cate)

	cate := db.Find(&MovieCate{}, id)
	fmt.Println(cate)
}

// 条件查询
func getCateByApiId(api_id int) {
	res := db.Where("api_id =?", api_id).Order("id desc").First(&MovieCate{})
	fmt.Println(res)
}

// 更新数据
func update() {
	var cate MovieCate
	//db.First(&cate, 723)
	//cate.TypeName = "我被更新了"
	//db.Save(&cate)

	db.Model(&cate).Where("id = ?", 724).Updates(MovieCate{
		TypeName: "我又被更新了",
		Status:   0,
	})
}

// 删除数据
func delete(id int) {
	db.Delete(&MovieCate{}, id)
}

func main() {
	delete(723)
}

处理json字段

package models

import (
	"encoding/json"
	"github.com/google/uuid"
	"gorm.io/gorm"
)

// 接口表
type MovieApi struct {
	TableId
	Name      string `gorm:"column:name;not null;comment:接口名称" json:"name"`// 接口名称
	URL       string `gorm:"column:url;not null;comment:接口地址" json:"url"` // 接口地址
	UUID      string `gorm:"column:uuid;not null" json:"uuid"`
    
	Cate      []byte `gorm:"column:cate;comment:视频分类;type:json" json:"-"` // 视频分类
	CateList []MovieCate `gorm:"-" json:"cateList"` // 显示的时候输出
	TableTime
}

func (w MovieApi) Comment() string {
	return "ALTER TABLE movie_api COMMENT='视频接口';"
}

// 创建对象
func (u *MovieApi) BeforeCreate(tx *gorm.DB) (err error) {
	u.UUID = uuid.NewString()
	return nil
}

// 将存储的cate数据解析为我们想要的结果
func (u *MovieApi) AfterFind(tx *gorm.DB) (err error) {
	var cate []MovieCate
	json.Unmarshal(u.Cate, &cate)
	u.CateList = cate
	return nil
}

关联统计

需求：我想通过电影的分类来查询某个分类下面的电影总数是多少

想看sql语句的查询，注意语法顺序主表是cate表，关联movies 查询

SELECT
  a.*,
  COUNT( m.id ) AS movie_total 
FROM
  movie_apis a
  LEFT JOIN movies m ON a.id = m.api_id 
  WHERE a.name like '%115%' and is_default = 0 
GROUP BY
  a.id

gorm查询

// 单独定义一个结构体出来
type MovieApiData struct {
	models.MovieApi
	MovieTotal int64 `json:"movie_total"` // 这里的名称不要跟已有的名称重名
}

func (a MovieApiController) List(c *gin.Context) {
	name := c.Query("name")
	where := map[string]interface{}{}
	if name != "" {
		where["name"] = name
	}
	
	var movieApiData []MovieApiData
	if err := database.DB.Table(models.TableNameMovieApis).Select("movie_apis.*,COUNT(movies.id) as movie_total").
		Joins("LEFT JOIN movies ON movies.api_id = movie_apis.id").
		Where(where).
		Scopes(models.Paginate(c)).
		Group("movie_apis.id").
		Scan(&movieApiData).Error; err != nil {
		resp.Error(c, err.Error())
		return
	}

    apiList := []models.MovieApi{}
	for _, item := range movieApiData {
		item.MovieCount = item.MovieTotal
		apiList = append(apiList, item.MovieApi)
	}

    var total int64
	database.DB.Model(models.MovieApi{}).Where(where).Count(&total)
	admin.RespList(c, apiList, total)
}

返回结果

{
    "code": 200,
    "data": [
        {
            "id": 38,
            "name": "xxx",
            "url": "https://xxx",
            "status": 1,
            "is_vip": 1,
            "parse_url": "https://xxx",
            "is_default": 0,
            "uuid": "b7109595-377b-4e7b-982e-cf4c62903f21",
            "cateList": null,
            "movie_count": 39350, // 这里是关联查询出来的值
            "created_at": "2024-04-29 09:47:28",
            "updated_at": "2024-06-04 15:49:13"
        }]
}

快捷键

func (a *MapData) Comment() string {
	return "ALTER TABLE $name$ COMMENT='$desc$';"
}
enum("users","admins","cate","articles","websites","settings")
enum("用户","管理员","分类","文章","网站配置","设置表")


orm-fileds
gorm:"column:$column$;type:$type$($type_number$);$null$comment:$comment$"
enum("name","phone","status","remark")
enum("名称","手机号","状态","备注")
enum("varchar","int","tinyint","text")
enum("255","100","50","20")
enum("not null;")