1、在model.biz的静态构造类中添加分库分表策略
static History() { // 添加分库分表策略 Meta.ShardPolicy = new TimeShardPolicy(nameof(CreateTime), Meta.Factory) { ConnPolicy = "HDB_{1:yyMM}", TablePolicy = "History_{1:yyMMdd}", Step = TimeSpan.FromDays(1), }; Meta.Modules.Add<UserModule>(); Meta.Modules.Add<TimeModule>(); Meta.Modules.Add<IPModule>(); }
2、构造测试数据
static void TestByDate() { XTrace.WriteLine("按时间分表分库,每月一个库,每天一张表"); // 预先准备好各个库的连接字符串,动态增加,也可以在配置文件写好 var start = DateTime.Today; for (var i = 0; i < 12; i++) { var dt = new DateTime(start.Year, i + 1, 1); var connName = $"HDB_{dt:yyMM}"; DAL.AddConnStr(connName, $"data source=timeData\\{connName}.db", null, "sqlite"); } // 每月一个库,每天一张表 start = new DateTime(start.Year, 1, 1); for (var i = 0; i < 365; i++) { var dt = start.AddDays(i); History.Meta.ConnName = $"HDB_{dt:yyMM}"; History.Meta.TableName = $"History_{dt:yyMMdd}"; // 插入一批数据 var list = new List<History>(); for (var n = 0; n < 1000; n++) { var entity = new History { Category = "交易", Action = "转账", CreateUserID = 1234, CreateTime = dt, Remark = $"[{Rand.NextString(6)}]向[{Rand.NextString(6)}]转账[¥{Rand.Next(1_000_000) / 100d}]" }; list.Add(entity); } // 批量插入。两种写法等价 //list.BatchInsert(); list.Insert(true); } }
3、测试跨库跨表数据查询
static void Search() { var start = DateTime.Today; for (var i = 0; i < 12; i++) { var dt = new DateTime(start.Year, i + 1, 1); var connName = $"HDB_{dt:yyMM}"; DAL.AddConnStr(connName, $"data source=timeData\\{connName}.db", null, "sqlite"); } //跨表 //var dt1 = new DateTime(start.Year, 1, 1); //var dt2 = new DateTime(start.Year, 1, 10); //跨库 var dt1 = Convert.ToDateTime("2021-1-31"); var dt2 = Convert.ToDateTime("2021-3-1"); var exp = new WhereExpression(); exp &= History._.CreateTime.Between(dt1, dt2); PageParameter pageParameter = new PageParameter { PageSize = 3000, PageIndex = 1, RetrieveTotalCount = true }; var list = History.FindAll(exp, pageParameter).OrderBy(x=>x.CreateTime).OrderBy(x=>x.ID); var allcount = pageParameter.TotalCount; var re = Newtonsoft.Json.JsonConvert.SerializeObject(list.OrderBy(x=>x.ID)); Console.WriteLine(re); Console.WriteLine(allcount); }