分表设计
基于 id 分表
package schema
import (
"errors"
"fmt"
"gorm.io/gorm"
"hash/crc32"
"time"
)
const (
TimeShards = 64 // 分表个数
)
type ScheduleTime struct {
ID uint `gorm:"primaryKey;column:id;comment:主键id"`
TalID string `gorm:"column:tal_id;index:idx_tal_id_start_end_time,priority:1;index:idx_tal_id_schedule_id,priority:1;index:idx_tal_id_schedule_date,priority:1;type:varchar(40);not null;default:'';comment:用户id"`
ScheduleDate time.Time `gorm:"column:schedule_date;index:idx_tal_id_schedule_date,priority:2;type:date;not null;default:'0000-00-00';comment:计划日期"`
StartTime int64 `gorm:"column:start_time;index:idx_tal_id_start_end_time,priority:2;type:bigint(20) unsigned;default:0;comment:开始时间"`
EndTime int64 `gorm:"column:end_time;index:idx_tal_id_start_end_time,priority:3;type:bigint(20) unsigned;default:0;comment:结束时间"`
ScheduleID string `gorm:"column:schedule_id;index:idx_tal_id_schedule_id,priority:2;type:varchar(50);not null;default:'';comment:计划id"`
GroupID string `gorm:"column:group_id;type:varchar(50);not null;default:'';comment:计划组id"`
GroupType uint8 `gorm:"column:group_type;type:tinyint(1) unsigned;default:0;comment:组类型：1模版 2活动 3规划 4专题任务"`
FinishTime int64 `gorm:"column:finish_time" json:"finish_time"`
TimeSchema
}
// Sharding 分片
func (s *ScheduleTime) Sharding() func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
if s.TalID == "" {
_ = db.AddError(errors.New("talID is required"))
return db
}
id := int(crc32.ChecksumIEEE([]byte(s.TalID)))
tableName := fmt.Sprintf("%s_%d", s.TableName(), id%TimeShards+1000)
return db.Table(tableName)
}
}
// TableShardName 分表名称
func (s *ScheduleTime) TableShardName() string {
id := int(crc32.ChecksumIEEE([]byte(s.TalID)))
return fmt.Sprintf("%s_%d", s.TableName(), id%TimeShards+1000)
}
func (*ScheduleTime) TableName() string {
return "sched_schedule_time"
}
基于 时间月分表
package schema
import (
"base-schedule-service/internal/application/helper"
"errors"
"fmt"
"gorm.io/gorm"
"strings"
"time"
)
const (
ScheduleVersionLabelOld = "sched_"
ScheduleVersionLabel = "sched1_"
ScheduleTableName = "sched_schedule"
)
// Schedule 计划表
type Schedule struct {
ID uint `gorm:"primaryKey;column:id" json:"id"` // 主键id
ScheduleId string `gorm:"column:schedule_id" json:"schedule_id"` // 计划id
ScheduleName string `gorm:"column:schedule_name" json:"schedule_name"` // 计划名称
ScheduleDate time.Time `gorm:"column:schedule_date" json:"schedule_date"` // 计划日期
SourceType int8 `gorm:"column:source_type" json:"source_type"` // 来源类型
SourceId string `gorm:"column:source_id" json:"source_id"` // 来源id
TalId string `gorm:"column:tal_id" json:"tal_id"` // 用户id
StartTime int64 `gorm:"column:start_time" json:"start_time"` // 开始时间
EndTime int64 `gorm:"column:end_time" json:"end_time"` // 结束时间
Status int8 `gorm:"column:status" json:"status"` // 状态
IsDeleted int8 `gorm:"column:is_deleted" json:"is_deleted"` // 软删除
CreatedAt time.Time `gorm:"column:created_at" json:"created_at"` // 创建时间
UpdatedAt time.Time `gorm:"column:updated_at" json:"updated_at"` // 修改时间
PeriodType int8 `gorm:"column:period_type" json:"period_type"` // 周期类型
TemplateId uint `gorm:"column:template_id" json:"template_id"` // 模版id
ScheduleTask []ScheduleTask `gorm:"foreignKey:ScheduleId;references:ScheduleId"`
}
// Sharding 分片
func (s *Schedule) Sharding() func(db *gorm.DB) *gorm.DB {
return func(db *gorm.DB) *gorm.DB {
if s.ScheduleId == "" {
_ = db.AddError(errors.New("ScheduleId is required"))
return db
}
if strings.HasPrefix(s.ScheduleId, ScheduleVersionLabel) {
t, err := helper.ParseUUIDTime(s.ScheduleId, ScheduleVersionLabel)
if err != nil {
_ = db.AddError(errors.New("ParseUUIDTime is error:" + err.Error()))
return db
}
tableName := fmt.Sprintf("%s_%s", ScheduleTableName, t.Format("200601"))
return db.Table(tableName)
}
return db.Table(s.TableName())
}
}
// BeforeCreate 创建前检查
func (s *Schedule) BeforeCreate(db *gorm.DB) (err error) {
if !strings.HasPrefix(s.ScheduleId, ScheduleVersionLabel) {
return
}
t, err := helper.ParseUUIDTime(s.ScheduleId, ScheduleVersionLabel)
if err != nil {
return err
}
// 使用uuid中的时间
s.CreatedAt = t
s.UpdatedAt = t
return
}
// AfterCreate 添加schedule成功，事务操作同步数据到schedule_time表
func (s *Schedule) AfterCreate(db *gorm.DB) (err error) {
if strings.HasPrefix(s.ScheduleId, ScheduleVersionLabel) {
var scheduleTime = &ScheduleTime{
TalID: s.TalId,
ScheduleDate: s.ScheduleDate,
StartTime: s.StartTime,
EndTime: s.EndTime,
ScheduleID: s.ScheduleId,
GroupID: s.SourceId,
GroupType: uint8(s.SourceType),
TimeSchema: TimeSchema{
CreatedAt: s.CreatedAt,
UpdatedAt: s.UpdatedAt,
},
}
if err = db.Scopes(scheduleTime.Sharding()).Create(scheduleTime).Error; err != nil {
return errors.New("Schedule AfterCreate Create Error: " + err.Error())
}
}
return nil
}
// TableShardName 分表名称
func (s *Schedule) TableShardName() string {
if strings.HasPrefix(s.ScheduleId, ScheduleVersionLabel) {
if t, err := helper.ParseUUIDTime(s.ScheduleId, ScheduleVersionLabel); err == nil {
return fmt.Sprintf("%s_%s", ScheduleTableName, t.Format("200601"))
}
}
return s.TableName()
}
func (*Schedule) TableName() string {
return ScheduleTableName + "_197001"
}
type UpdateScheduleTime struct {
ScheduleID string `json:"schedule_id"`
StartTime int64 `json:"start_time"`
EndTime int64 `json:"end_time"`
PeriodType int `json:"period_type"`
ScheduleDate string `json:"schedule_date"`
}
package helper
import (
"encoding/binary"
"encoding/hex"
"github.com/google/uuid"
"math/rand"
"strings"
"time"
)
const (
lillian = 2299160 // Julian day of 15 Oct 1582
unix = 2440587 // Julian day of 1 Jan 1970
epoch = unix - lillian // Days between epochs
g1582 = epoch * 86400 // seconds between epochs
g1582ns100 = g1582 * 10000000 // 100s of a nanoseconds between epochs
)
func GenUUID(delimiter string) (string, time.Time, error) {
// 生成一个随机的节点ID
nodeID := []byte{
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
}
// 设置节点id，当发生时钟回拨，重复的概率是2的48次方，也就是42.9亿*65535左右
uuid.SetNodeID(nodeID)
// 生成uuid
id, err := uuid.NewUUID()
if err != nil {
return "", time.Time{}, err
}
// 解析uuid
parsedID, err := uuid.Parse(id.String())
if err != nil {
return "", time.Time{}, err
}
var dst [32]byte
_ = hex.Encode(dst[:], id[:])
// 获取时间戳
unixTime, _ := parsedID.Time().UnixTime()
return delimiter + string(dst[:]), time.Unix(unixTime, 0), nil
}
func ParseUUIDTime(id, delimiter string) (time.Time, error) {
pos := strings.Index(id, delimiter)
if pos != -1 {
id = id[pos+len(delimiter):]
}
// 解析uuid
parsedID, err := uuid.Parse(id)
if err != nil {
return time.Time{}, err
}
// 获取时间戳
unixTime, _ := parsedID.Time().UnixTime()
return time.Unix(unixTime, 0), nil
}
// GenUUIDByUnixTime 指定时间戳生成uuid
func GenUUIDByUnixTime(delimiter string, unixTime int64) (string, error) {
// 指定生成时间戳
t := time.Unix(unixTime, int64(time.Now().Nanosecond()))
now, seq, err := uuid.GetTime()
if err != nil {
return "", err
}
now = uuid.Time(t.UnixNano()/100) + g1582ns100
// 生成一个随机的节点ID
nodeID := []byte{
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
byte(rand.Intn(256)),
}
// 设置节点id，当发生时钟回拨，重复的概率是2的48次方，也就是42.9亿*65535左右
uuid.SetNodeID(nodeID)
newUUID, err := uuid.NewUUID()
if err != nil {
return "", err
}
timeLow := uint32(now & 0xffffffff)
timeMid := uint16((now >> 32) & 0xffff)
timeHi := uint16((now >> 48) & 0x0fff)
timeHi |= 0x1000 // Version 1
binary.BigEndian.PutUint32(newUUID[0:], timeLow)
binary.BigEndian.PutUint16(newUUID[4:], timeMid)
binary.BigEndian.PutUint16(newUUID[6:], timeHi)
binary.BigEndian.PutUint16(newUUID[8:], seq)
var dst [32]byte
_ = hex.Encode(dst[:], newUUID[:])
return delimiter + string(dst[:]), nil
}