sharding key or id required, and use operator =

[zishiguo]

Your Question

sharding key or id required, and use operator =

sharding rules as follows

• user_id use Snowflake id，such as 79535114761158382

• md5(user_id)

• get first one letter for schema name, then join wkread_users_, such as wkread_users_a

• every database host has two schema, such as wkread_users_2,wkread_users_3in the db06 host

• first two letters for table name, then join user_, such as tables user_30,user_31``user_32``user_33``user_34``user_35``user_36``user_37``user_38``user_39``user_3a``user_3b``user_3c``user_3d``user_3e``user_3f in the schema wkread_users_3

Sharding is as follows in my project

db05
  wkread_users_0
       user_00
       user_01
         ...
       user_0f
  wkread_users_1
       user_10
       user_11
         ...
       user_1f
db06
  wkread_users_2
       user_20
       user_21
         ...
       user_2f
  wkread_users_3
       user_30
       user_31
         ...
       user_3f
...
db12
  wkread_users_e
       user_e0
       user_e1
         ...
       user_ef
  wkread_users_f
       user_f0
       user_f1
         ...
       user_ff

database conf

default:
  master:
    Name: express
    Addr: 10.131.32.124:30001
    UserName: root
    Password: hhel0315
db05-wkread_users_0:
  master:
    Name: wkread_users_0
    Addr: 10.131.32.124:30005
    UserName: root
    Password: hhel0315
db05-wkread_users_1:
  master:
    Name: wkread_users_1
    Addr: 10.131.32.124:30005
    UserName: root
    Password: hhel0315
db06-wkread_users_2:
  master:
    Name: wkread_users_2
    Addr: 10.131.32.124:30006
    UserName: root
    Password: hhel0315
db06-wkread_users_3:
  master:
    Name: wkread_users_3
    Addr: 10.131.32.124:30006
    UserName: root
    Password: hhel0315
db07-wkread_users_4:
  master:
    Name: wkread_users_4
    Addr: 10.131.32.124:30007
    UserName: root
    Password: hhel0315
db07-wkread_users_5:
  master:
    Name: wkread_users_5
    Addr: 10.131.32.124:30007
db08-wkread_users_6:
  master:
    Name: wkread_users_6
    Addr: 10.131.32.124:30008
db08-wkread_users_7:
  master:
    Name: wkread_users_7
    Addr: 10.131.32.124:30008
db09-wkread_users_8:
  master:
    Name: wkread_users_8
    Addr: 10.131.32.124:30009
db09-wkread_users_9:
  master:
    Name: wkread_users_9
    Addr: 10.131.32.124:30009
db10-wkread_users_a:
  master:
    Name: wkread_users_a
    Addr: 10.131.32.124:30010
db10-wkread_users_b:
  master:
    Name: wkread_users_b
    Addr: 10.131.32.124:30010
db11-wkread_users_c:
  master:
    Name: wkread_users_c
    Addr: 10.131.32.124:30011
db11-wkread_users_d:
  master:
    Name: wkread_users_d
    Addr: 10.131.32.124:30011
db12-wkread_users_e:
  master:
    Name: wkread_users_e
    Addr: 10.131.32.124:30012
    UserName: root
    Password: hhel0315
db12-wkread_users_f:
  master:
    Name: wkread_users_f
    Addr: 10.131.32.124:30012

database dialector is mysql

func NewReadWriteSplits(cm map[string]ReadWriteSplitConfig) (db *gorm.DB) {
    defaultConfig := cm["default"]
    defaultMasterConfig := defaultConfig.Master

    dialector := NewDialectorWithPool(&defaultMasterConfig)

    db, err := gorm.Open(dialector, gormConfig(&defaultMasterConfig))
    if err != nil {
        log.Panicf("database connection failed. database name: %s, err: %+v", defaultMasterConfig.Name, err)
    }
    db.Set("gorm:table_options", "CHARSET=utf8mb4")
    // default
    resolver := dbresolver.Register(dbresolver.Config{
        Replicas: func(cs []Config) []gorm.Dialector {
            var ds []gorm.Dialector
            for _, c := range cs {
                ds = append(ds, NewDialectorWithPool(&c))
            }
            return ds
        }(defaultConfig.Slaves),
        TraceResolverMode: true,
    })
    // other
    for name, c := range cm {
        if name == "default" {
            break
        }
        resolver = resolver.Register(dbresolver.Config{
            Sources: func(c Config) []gorm.Dialector {
                ds := []gorm.Dialector{
                    NewDialector(&c),
                }
                return ds
            }(c.Master),
            Replicas: func(cs []Config) []gorm.Dialector {
                var ds []gorm.Dialector
                for _, c := range cs {
                    ds = append(ds, NewDialector(&c))
                }
                return ds
            }(c.Slaves),
            TraceResolverMode: true,
        }, name).SetMaxOpenConns(c.Master.MaxOpenConn).
            SetMaxIdleConns(c.Master.MaxIdleConn).
            SetConnMaxLifetime(c.Master.ConnMaxLifeTime).
            SetConnMaxIdleTime(c.Master.ConnMaxIdleTime)
    }

    db.Use(resolver)

    return db
}

query a user by uid code as follows

func (d *repository) GetUser(ctx context.Context, uid int64) (*model.User, error) {
    var data model.User
    resolverName := data.GetDBResolverName(uid)
    db := d.orm.WithContext(ctx).Clauses(dbresolver.Use(string(resolverName)))
    err := db.Use(sharding.Register(sharding.Config{
        ShardingKey:         "id",
        NumberOfShards:      16,
        PrimaryKeyGenerator: sharding.PKSnowflake,
        ShardingAlgorithmByPrimaryKey: func(id int64) (suffix string) {
            hashed, _ := utils.Md5(fmt.Sprintf("%d", id))
            fmt.Printf("id:%d, hashed:%s\n", id, hashed)
            return fmt.Sprintf("_%s", hashed[:2])
        },
    }, model.User{}))
    if err != nil {
        return nil, errors.Wrapf(err, "[repo.user] sharding.Register err")
    }
    err = db.First(&data, uid).Error
    if errors.Is(err, ErrNotFound) {
        return nil, ErrNotFound
    } else if err != nil {
        return nil, errors.Wrapf(err, "[repo.user] query db err")
    }
    return &data, nil
}

get an error when run

2023/02/13 20:13:11 /Users/zhengjb/go/src/gitlab.y5ops.com/jin/hertz_demo/biz/repository/user_repo.go:30 sharding key or id required, and use operator =
[0.141ms] [rows:0] [replica] SELECT * FROM `user` WHERE `user`.`id` = 79535114761158382 ORDER BY `user`.`id` LIMIT 1

The document you expected this should be explained

Can not db.Use used when db.Clauses is used ?

cannot use Clauses

Expected answer

get a user as expected. or how to do as described ?