-- 創(chuàng)建分庫(kù)
CREATEDATABASEIFNOTEXISTS`db_0`;
CREATEDATABASEIFNOTEXISTS`db_1`;

-- 在db_0中創(chuàng)建分表
USE`db_0`;
CREATETABLE`users_202504` (
`id`BIGINT PRIMARY KEY,
`name`VARCHAR(50),
`created_at` DATETIME
);

-- 在db_1中創(chuàng)建相同結(jié)構(gòu)的表
USE`db_1`;
CREATETABLE`users_202504` (
`id`BIGINT PRIMARY KEY,
`name`VARCHAR(50),
`created_at` DATETIME
);

// internal/db/shard_pool.go
package db

import (
"fmt"

"gorm.io/driver/mysql"
"gorm.io/gorm"
)

var ShardPool = make(map[int]*gorm.DB)

func InitShardPool() {
// 分庫(kù)配置（實(shí)際生產(chǎn)環(huán)境應(yīng)從配置文件讀取）
 shardConfigs := map[int]string{
0: "root:123456@tcp(127.0.0.1:3306)/db_0?charset=utf8mb4&parseTime=True",
1: "root:123456@tcp(127.0.0.1:3306)/db_1?charset=utf8mb4&parseTime=True",
 }

for shardID, dsn := range shardConfigs {
  db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
   PrepareStmt: true, // 開(kāi)啟預(yù)編譯提升性能
  })

if err != nil {
   panic(fmt.Sprintf("連接分庫(kù)%d失敗: %v", shardID, err))
  }

// 配置連接池參數(shù)
  sqlDB, _ := db.DB()
  sqlDB.SetMaxOpenConns(20)
  sqlDB.SetMaxIdleConns(10)

  ShardPool[shardID] = db
 }
}

// internal/sharding/rule.go
func GetShard(userID int64) (shardID int, tableName string) {
    // 分庫(kù)規(guī)則：user_id取模
    shardID = int(userID % 2)
    
    // 分表規(guī)則：按創(chuàng)建時(shí)間取年月
    now := time.Now()
    tableName = fmt.Sprintf("order_%s", now.Format("200601"))
    
    return
}

// internal/model/user.go
package model

import (
"gorm-demo/internal/db"
"gorm-demo/internal/sharding"
"time"
)

type User struct {
 ID        int64`gorm:"primaryKey"`
 Name      string
 CreatedAt time.Time
}

// CreateUser 插入分庫(kù)分表數(shù)據(jù)
func CreateUser(user *User) error {
 shardID, tableName := sharding.GetShard(user.ID)
 db := db.ShardPool[shardID]
return db.Table(tableName).Create(user).Error
}

// QueryUser 查詢分庫(kù)分表數(shù)據(jù)
func QueryUser(userID int64) (*User, error) {
 shardID, tableName := sharding.GetShard(userID)
 db := db.ShardPool[shardID]

var user User
 err := db.Table(tableName).Where("id = ?", userID).First(&user).Error
return &user, err
}

package main

import (
"fmt"
"gorm-demo/internal/db"
"gorm-demo/internal/model"
"time"
)

func main() {
// 初始化分庫(kù)連接池
 db.InitShardPool()
deferfunc() {
for _, db := range db.ShardPool {
   sqlDB, _ := db.DB()
   sqlDB.Close()
  }
 }()

// 測(cè)試數(shù)據(jù)插入
 users := []model.User{
  {ID: 1001, Name: "Alice", CreatedAt: time.Date(2025, 4, 10, 0, 0, 0, 0, time.UTC)},
  {ID: 1002, Name: "Bob", CreatedAt: time.Date(2025, 4, 10, 0, 0, 0, 0, time.UTC)},
 }

for _, u := range users {
if err := model.CreateUser(&u); err != nil {
   fmt.Printf("Insert error: %v\n", err)
  }
 }

// 測(cè)試查詢
if user, err := model.QueryUser(1001); err == nil {
  fmt.Printf("Query result: %+v\n", user)
 }
}