【Go实战 电商平台】(3) 数据库建表 写在前面 1

发表于

这是我参与11月更文挑战的第23天，活动详情查看：2021最后一次更文挑战

写在前面

第一节，我们确定了ER图，数据字典。
第二节，我们已经配置了MySQL。
结合前两章，我们就可以建立数据库表了。

  1. 初始化连接

在这里插入图片描述

  • init.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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()
}

  1. 建立表

  • user

用户表

1
2
3
4
5
6
7
8
9
10
go复制代码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 商品表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
go复制代码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 商品图片表
1
2
3
4
5
6
go复制代码type ProductImg struct {
	gorm.Model
	Product Product `gorm:"ForeignKey:ProductID"`
	ProductID  uint `gorm:"not null"`
	ImgPath    string
}
  • ProductParamImg 商品参数表
1
2
3
4
5
6
go复制代码type ProductParamImg struct {
	gorm.Model
	Product Product `gorm:"ForeignKey:ProductID"`
	ProductID  uint `gorm:"not null"`
	ImgPath   string
}
  • ProductInfoImg 商品详情表
1
2
3
4
5
6
go复制代码type ProductInfoImg struct {
	gorm.Model
	Product Product `gorm:"ForeignKey:ProductID"`
	ProductID  uint `gorm:"not null"`
	ImgPath   string
}
  • 购物车表
1
2
3
4
5
6
7
8
9
10
go复制代码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 订单表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
go复制代码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
}
  • 地址表
1
2
3
4
5
6
7
8
go复制代码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"`
}
  • 收藏表
1
2
3
4
5
6
7
8
9
go复制代码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"`
}
  • 分类表
1
2
3
4
go复制代码type Category struct {
	gorm.Model
	CategoryName string
}
  • 管理员表
1
2
3
4
5
6
go复制代码type Admin struct {
	gorm.Model
	UserName       string
	PasswordDigest string
	Avatar         string `gorm:"size:1000"`
}
  • 轮播图表
1
2
3
4
5
6
go复制代码type Carousel struct {
	gorm.Model
	ImgPath   string
	Product Product `gorm:"ForeignKey:ProductID"`
	ProductID uint `gorm:"not null"`
}

  1. 迁移

  • 做迁移并加上外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
go复制代码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函数是添加外键

举例子

1
go复制代码DB.Model(&Cart{}).AddForeignKey("product_id","Product(id)","CASCADE","CASCADE")

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

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%