【Go實戰 | 電商平台】(3) 資料庫建表

文章目錄

寫在前面

1. 初始化連接配接

2. 建立表

3. 遷移

第一節，我們确定了ER圖，資料字典。

第二節，我們已經配置了MySQL。

結合前兩章，我們就可以建立資料庫表了。

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()
}      

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"`
}      

做遷移并加上外鍵限制

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函數是添加外鍵

舉例子

DB.Model(&Cart{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")      

這個函數就是在Cart表中把product_id字段關聯到product的id中，後面兩個就是update和delete的時候進行級聯更新或是級聯删除。