database – IT漫步

Use regex to match and replace in MySql 8.0+ or MariaDB

2021年10月22日2021年10月22日 by ylhyh

UPDATE server_list_server
  SET world_id=regexp_replace(ip,'wg(\\d+)\.glwsy\.szgla\.com;?','\\1')
  WHERE ip REGEXP 'wg(\\d+)\.glwsy\.szgla\.com;?'
    AND world_id!=regexp_replace(ip,'wg(\\d+)\.glwsy\.szgla\.com;?','\\1');

REGEXP
regexp_replace
\\1 is the placeholder for matched group

Change value for a PK column with AUTO_INCREMENT in mysql

2020年9月30日 by ylhyh

show create table TABLE_NAME; // Check current AUTO_INCREMENT value
update IGNORE TABLE_NAME set primary_field = 'value' ...; // Change the column value with IGNORE keyword
alter table TABLE_NAME AUTO_INCREMENT = 50; // Set AUTO_INCREMENT
show create table TABLE_NAME;  // Check current AUTO_INCREMENT value again

Aggregate in Mongodb in C#

2019年5月10日2019年5月10日 by ylhyh

Filter record from collection “OperationSession”, sort by “WorldId” descending, then group by “WorldId”, then pick first record from each group, then sort result:

Way #1:

db.getCollection('OperationSession').aggregate(
[
  { "$match": {"ActivityId":74,"GameId":2109} },
  { "$sort":{ "CreateTime" : -1} },
  { "$group":
    { 
        _id:"$WorldId",
        SessionId:{"$first": "$_id" },
        GameId:{"$first": "$GameId" },
        WorldId:{"$first": "$WorldId" },
        ActivityId:{"$first": "$ActivityId" },
        Type:{"$first": "$Type" },
        Status:{"$first": "$Status" },
        ActivityStatus:{"$first": "$ActivityStatus" }
     }
   },
   { "$sort":{ "WorldId" : 1} },
   { "$skip": 20},
   { "$limit": 10}
  ]
)

Way #2:

db.OperationSession.aggregate()
      .match({"ActivityId":74,"GameId":2109})
      .sort({"CreateTime":-1})
      .group({
            "_id":"$WorldId",
            "SessionId":{"$first": "$_id" },
            "GameId":{"$first": "$GameId" },
            "WorldId":{"$first": "$WorldId" },
            "ActivityId":{"$first": "$ActivityId" },
            "Type":{"$first": "$Type" },
            "Status":{"$first": "$Status" },
            "ActivityStatus":{"$first": "$ActivityStatus" }
        })
      .sort({"WorldId":1})
      .skip(20)
      .limit(10)

In C#:

collection.Aggregate<DataEntity.OperationSession>()
                .Match(s => s.ActivityId == 74 && s.GameId == 2109)
                .SortByDescending(s => s.CreateTime)
                .Group(
                    s => s.WorldId,
                    s => new Interface.OperationSession
                    {
                        SessionId = s.Select(x => x.Id).First(),
                        GameId = s.Select(x => x.GameId).First(),
                        WorldId = s.Select(x => x.WorldId).First(),
                        ActivityId = s.Select(x => x.ActivityId).First(),
                        Type = s.Select(x => x.Type).First(),
                        Status = s.Select(x => x.Status).First(),
                        ActivityStatus = s.Select(x => x.ActivityStatus).First()
                    })
                .SortBy(s => s.WorldId)
                .Skip(20)
                .Limit(10).ToList();

[MongoDB] Read preference in a transaction must be primary

2019年2月14日 by ylhyh

MongoDB 4.0.3 ReplicaSet

MongoDB.Driver for .net Core 2.7.3

在Connection String中配置了readPreference=secondaryPreferred:

mongodb://user:password@mongod-1:27017,mongod-2:27017,mongod-3:27017/DBName?connect=replicaSet&readPreference=secondaryPreferred

然后在一个Transaction中读取数据报错：”Read preference in a transaction must be primary”, Transaction中的读取不能使用secondaryPreferred

MongoDB中使用$rename指令修改字段名

2018年9月21日 by ylhyh

如下所示：把collection Policy中的Type为1的所有document的UserID字段修改为UserName, multi为true表明修改所有记录，upsert为false表明记录不存在则不创建：

db.getCollection('Policy').update({"Type":1},{$rename:{'UserID': 'UserName'}},{"multi" : true, "upsert" : false})

参考：https://docs.mongodb.com/manual/reference/operator/update/rename/