TableHelper用法概述

支持热更新

配置:

{
    "mysql2_beenest":{
        "dsn": "root:602gogogo@tcp(172.16.1.220:3306)/beenest?charset=utf8mb4",
        "maxOpenConns": 100,
        "maxIdleConns": 2
    }
}

检查更新代码:

func GetDB(dbKey string) *stmtsDB {
    mu.RLock()
    var dbConf = getDbConf(dbKey)
    if dbMap[dbKey] != nil {
        defer mu.RUnlock()
        // 检查数据库配置是否更新
        checkConfigUpate(dbKey, dbConf)
        return dbMap[dbKey]
    }

    mu.RUnlock()
    mu.Lock()
    defer mu.Unlock()

    if dbMap[dbKey] == nil {
        dbMap[dbKey] = newDB2(dbConf)
    }

    return dbMap[dbKey]
}

func checkConfigUpate(dbKey string, dbConf *DbConf) {
    db := dbMap[dbKey]
    if db.dbConf.Dsn != dbConf.Dsn {
        // 数据库链接不一样,需要重新初始化
        db2 := newDB2(dbConf)
        dbMap[dbKey] = db2
        xlog.Warn2("mysql dsn change, key:" + dbKey + " old:",  db.dbConf.Dsn, ", new:", dbConf.Dsn)
        // 关闭老数据库的链接
        db.Close()
        xlog.Warn2("mysql close old db:",  db.dbConf.Dsn)
    }

    if db.dbConf.MaxIdleConns != dbConf.MaxIdleConns {
        xlog.Warn2("mysql maxIdleConns change, key:" + dbKey + " old:",  db.dbConf.MaxIdleConns, ", new:", dbConf.MaxIdleConns)
        db.SetMaxIdleConns(int(dbConf.MaxIdleConns))
        db.dbConf.MaxIdleConns = dbConf.MaxIdleConns
    }

    if db.dbConf.MaxOpenConns != dbConf.MaxOpenConns {
        xlog.Warn2("mysql maxOpenConns change, key:" + dbKey + " old:",  db.dbConf.MaxOpenConns, ", new:", dbConf.MaxOpenConns)
        db.SetMaxOpenConns(int(dbConf.MaxOpenConns))
        db.dbConf.MaxOpenConns = dbConf.MaxOpenConns
    }
}

动态构造Sql语句

objTable := getTable()

// `channel_id` = 1 AND `id` IN (1, 3, 4, 6)
where := Map{
  "id":         []int{1, 3, 4, 6},
  "channel_id": 1,
}

kw := NewKeyWord()
// SELECT `id`, name
kw.Field = "`id`, name"
// WHERE created_at > 1
kw.Where2 = List{"created_at >= ?", 1}
// LIMIT 10, 100
kw.Limit = "10, 100"
// ORDER BY id DESC
kw.OrderBy = "`id` DESC"
// 阿里云强制使用Master节点,/*FORCE_MASTER*/ 
kw.ForceMaster = true

// /*FORCE_MASTER*/ SELECT `id`, `name` FROM `categories_test` WHERE `creator_id` = 2 AND created_at > 1 ORDER BY id DESC LIMIT 10, 100
var chList []*CategoriesTest
err := objTable.GetAll(context.Background(), where, &chList, kw)

缺陷:

  • IN个数不定,可能会导致 sql模板过多,@TODO 超过100个 可直接用sql语句执行。

  • 最好要先绑定entity对象,不然数据表增加字段时,Select * 有兼容性问题:

    xmysql.InitTableEntity("categories_test", "beenest", &entity.UserEmoji{})
    

优雅支持事务,方式一

  o := model.NewUserBlockedModel()
  // 开启事务
    ctx = o.Begin(ctx)
    defer o.Rollback(ctx)
    // 添加屏蔽记录
    o.Replace(ctx, &entity.UserBlocked{
        UserID:     req.UserID,
        BlockedUid: req.FriendUserID,
        CreatedAt:  xtime.Now(),
    })

    // 删除双方的请求
    o2 := model.NewFriendRequestModel()
    o2.DeletePendingState(ctx,  req.FriendUserID, req.UserID)
    o2.DeletePendingState(ctx,  req.UserID, req.FriendUserID)

    // 删除好友关系
    model.NewFriendModel().DeleteFriend(ctx, req.UserID, req.FriendUserID)
  // 提交事务
    o.GetTableX().Commit(ctx)

优雅支持事务,方式二

  o := model.NewUserBlockedModel()
  // 开启事务
    o.Trans(ctx, func(ctx context.Context) error {
      // 添加屏蔽记录
      o.Replace(ctx, &entity.UserBlocked{
          UserID:     req.UserID,
          BlockedUid: req.FriendUserID,
          CreatedAt:  xtime.Now(),
      })

      // 删除双方的请求
      o2 := model.NewFriendRequestModel()
      o2.DeletePendingState(ctx,  req.FriendUserID, req.UserID)
      o2.DeletePendingState(ctx,  req.UserID, req.FriendUserID)

      // 删除好友关系
      model.NewFriendModel().DeleteFriend(ctx, req.UserID, req.FriendUserID)

      return nil
  })

丰富的操作

image-20200728141429631

Copyright © xinyan all right reserved,powered by Gitbook该文件修订时间: 2020-08-03 18:14:28

results matching ""

    No results matching ""