【Go实战 | 电商平台】(3) 数据库建表

2022-03-24 13:06:11

文章目录

写在前面

1. 初始化连接

2. 建立表

3. 迁移

写在前面

第一节，我们确定了ER图，数据字典。

第二节，我们已经配置了MySQL。

结合前两章，我们就可以建立数据库表了。

【Go实战 | 电商平台】(3) 数据库建表

1. 初始化连接

init.go
package model
import (
    "github.com/gin-gonic/gin"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    "time"
)
var DB *gorm.DB
func Database(connString string) {
    db, err := gorm.Open("mysql", connString)
    db.LogMode(true) //GORM的打印
    if err != nil {
  panic(err)
    }
    if gin.Mode() == "release" {
  db.LogMode(false)
    }
    db.SingularTable(true)      //默认不加复数s
    db.DB().SetMaxIdleConns(20)     //设置连接池，空闲
    db.DB().SetMaxOpenConns(100)  //打开
    db.DB().SetConnMaxLifetime(time.Second * 30)
    DB = db
    migration()
}

2. 建立表

user

用户表

type User struct {
    gorm.Model
    UserName       string `gorm:"unique"`
    Email          string  //`gorm:"unique"`
    PasswordDigest string
    Nickname       string `gorm:"not null"`
    Status         string
    Avatar         string `gorm:"size:1000"`
    Money          int
}

product 商品表

type Product struct {
    gorm.Model
    Name          string `gorm:"size:255;index"`
    Category Category `gorm:"ForeignKey:CategoryID"`
    CategoryID    uint `gorm:"not null"`
    Title         string
    Info          string `gorm:"size:1000"`
    ImgPath       string
    Price         string
    DiscountPrice string
    OnSale     bool `gorm:"default:false"`
    Num     int
    BossID        int
    BossName      string
    BossAvatar    string
}

ProductImg 商品图片表

type ProductImg struct {
    gorm.Model
    Product Product `gorm:"ForeignKey:ProductID"`
    ProductID  uint `gorm:"not null"`
    ImgPath    string
}

ProductParamImg 商品参数表

type ProductParamImg struct {
    gorm.Model
    Product Product `gorm:"ForeignKey:ProductID"`
    ProductID  uint `gorm:"not null"`
    ImgPath   string
}

ProductInfoImg 商品详情表

type ProductInfoImg struct {
    gorm.Model
    Product Product `gorm:"ForeignKey:ProductID"`
    ProductID  uint `gorm:"not null"`
    ImgPath   string
}

购物车表

type Cart struct {
    gorm.Model
    UserID    uint
    Product Product `gorm:"ForeignKey:ProductID"`
    ProductID uint `gorm:"not null"`
    BossID    uint
    Num       uint
    MaxNum    uint
    Check     bool
}

Order 订单表

type Order struct {
    gorm.Model
    User    User    `gorm:"ForeignKey:UserID"`
    UserID       uint   `gorm:"not null"`
    Product      Product  `gorm:"ForeignKey:ProductID"`
    ProductID    uint   `gorm:"not null"`
    Boss   User     `gorm:"ForeignKey:BossID"`
    BossID   uint   `gorm:"not null"`
    Address   Address  `gorm:"ForeignKey:AddressID"`
    AddressID    uint   `gorm:"not null"`
    Num          uint
    OrderNum     uint64
    Type         uint
    Money    int
}

地址表

type Address struct {
    gorm.Model
    User  User `gorm:"ForeignKey:UserID"`
    UserID  uint `gorm:"not null"`
    Name    string `gorm:"type:varchar(20) not null"`
    Phone   string `gorm:"type:varchar(11) not null"`
    Address string `gorm:"type:varchar(50) not null"`
}

收藏表

type Favorite struct {
    gorm.Model
    User    User    `gorm:"ForeignKey:UserID"`
    UserID       uint   `gorm:"not null"`
    Product      Product  `gorm:"ForeignKey:ProductID"`
    ProductID    uint   `gorm:"not null"`
    Boss   User     `gorm:"ForeignKey:BossID"`
    BossID   uint   `gorm:"not null"`
}

分类表

type Category struct {
    gorm.Model
    CategoryName string
}

管理员表

type Admin struct {
    gorm.Model
    UserName       string
    PasswordDigest string
    Avatar         string `gorm:"size:1000"`
}

轮播图表

type Carousel struct {
    gorm.Model
    ImgPath   string
    Product Product `gorm:"ForeignKey:ProductID"`
    ProductID uint `gorm:"not null"`
}

3. 迁移

做迁移并加上外键约束

func migration() {
    //自动迁移模式
    DB.Set("gorm:table_options", "charset=utf8mb4").
  AutoMigrate(&User{}).
  AutoMigrate(&Product{}).
  AutoMigrate(&Carousel{}).
  AutoMigrate(&Category{}).
  AutoMigrate(&Favorite{}).
  AutoMigrate(&ProductImg{}).
  AutoMigrate(&ProductInfoImg{}).
  AutoMigrate(&ProductParamImg{}).
  AutoMigrate(&Order{}).
  AutoMigrate(&Cart{}).
  AutoMigrate(&Admin{}).
  AutoMigrate(&Address{})
    DB.Model(&Cart{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&Order{}).AddForeignKey("user_id","User(id)","CASCADE","CASCADE")
    DB.Model(&Order{}).AddForeignKey("address_id","Address(id)","CASCADE","CASCADE")
    DB.Model(&Order{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&Order{}).AddForeignKey("boss_id","User(id)","CASCADE","CASCADE")
    DB.Model(&Favorite{}).AddForeignKey("boss_id","User(id)","CASCADE","CASCADE")
    DB.Model(&Favorite{}).AddForeignKey("user_id","User(id)","CASCADE","CASCADE")
    DB.Model(&Favorite{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&Product{}).AddForeignKey("category_id","Category(id)","CASCADE","CASCADE")
    DB.Model(&ProductImg{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&ProductInfoImg{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&ProductParamImg{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")
    DB.Model(&Address{}).AddForeignKey("user_id","User(id)","CASCADE","CASCADE")
}

AutoMigrate函数是把代码映射到数据库中

AddForeignKey函数是添加外键

举例子

这个函数就是在Cart表中把product_id字段关联到product的id中，后面两个就是update和delete的时候进行级联更新或是级联删除。

码农公寓

相关文章