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 ?