net6 sqlsugar分表

表设计-------

/// 
/// 基站信息表/// [SplitTable(SplitType.Month)]//指定按照时间分表[SugarTable("BaseStationInfo_{year}{month}{day}")]public class BaseStationInfo{[SugarColumn(IsPrimaryKey = true)]public long Id { get; set; }/// 
/// 基站名称/// public string Name { set; get; }/// 
/// 礃子面ID/// public int ZhangZiZoneId { set; get; }/// 
/// IP/// public string Ip { set; get; }/// 
/// 端口/// public int Port { set; get; }/// 
/// 基站用户列表Json/// [SugarColumn(ColumnDataType = "text", IsNullable = true)]public string Users { get; set; }/// 
/// 状态（0=在线；1=离线）/// public int Status { set; get; }/// 
/// 是否删除/// public bool IsDeleted { get; set; } = false;/// 
/// 创建者Id/// public int CreateUserId { get; set; }/// 
/// 创建时间/// [SplitField]public DateTime CreateTime { get; set; } = DateTime.Now;/// 
/// 删除者Id/// [SugarColumn(IsNullable = true)]public int? DeleteUserId { get; set; }/// 
/// 删除时间/// [SugarColumn(IsNullable = true)]public DateTime? DeleteTime { get; set; }}

自动生成分表（插入数据时会自动生成分表，无需此代码）----

var db = new SqlSugarClient(new ConnectionConfig()
{ConnectionString = "server=192.168.3.103;Database=tunnel_core;Uid=xytech;Pwd=dfxwbz-420",DbType = SqlSugar.DbType.MySql,IsAutoCloseConnection = true,InitKeyType = InitKeyType.Attribute
});db.DbMaintenance.CreateDatabase();
db.CodeFirst.SplitTables().InitTables<BaseStationInfo>();

分表操作-----------

BaseStationInfo entity = new BaseStationInfo();
entity.Name = "测试10";
entity.ZhangZiZoneId = 1;
entity.Ip = "192.168.10.1";
entity.Port = 10;
entity.CreateUserId = 0;
entity.CreateTime = DateTime.Now;
//如果分表不存在会自动创建
var id = await db.Insertable(entity).SplitTable().ExecuteReturnSnowflakeIdAsync();//查询最近的3张表
var list = await db.Queryable<BaseStationInfo>().Where(t => t.IsDeleted == false).SplitTable(c => c.Take(3)).ToPageListAsync(1, 3);

//通过开始时间和结束时间自动生成CreateTime的过滤并且找到对应时间的表()
var list = await db.Queryable<BaseStationInfo>().SplitTable(DateTime.Now.AddMonths(-1), DateTime.Now).ToOffsetPageAsync(1, 3);//如下是Sql
SELECT * FROM  ( 
( SELECT `Id`,`Name`,`ZhangZiZoneId`,`Ip`,`Port`,`Users`,`Status`,`IsDeleted`,`CreateUserId`,`CreateTime`,`DeleteUserId`,`DeleteTime` FROM `BaseStationInfo_20230801`  WHERE  `CreateTime`>=@spBeginTimeUnionAll1 AND `CreateTime`<= @spEndTimeUnionAll1   )   
UNION ALL( SELECT `Id`,`Name`,`ZhangZiZoneId`,`Ip`,`Port`,`Users`,`Status`,`IsDeleted`,`CreateUserId`,`CreateTime`,`DeleteUserId`,`DeleteTime` FROM `BaseStationInfo_20230701`  WHERE  `CreateTime`>=@spBeginTimeUnionAll2 AND `CreateTime`<= @spEndTimeUnionAll2   )) unionTable      LIMIT 0,3; 

//根据时间获取表
var name = db.SplitHelper<BaseStationInfo>().GetTableName(entity.CreateTime);
var list2 = await db.Queryable<BaseStationInfo>().Where(t => t.IsDeleted == false).SplitTable(t => t.InTableNames(name)).ToListAsync();

//删除数据
var r = db.Deleteable<BaseStationInfo>().In(1686294891992322048).SplitTable(tas => tas.Take(3)).ExecuteCommand();

//更新近3张表
var r1 = db.Updateable(entity).SplitTable(tas => tas.Take(3)).ExecuteCommand();//精准找到表名并且更新数据
var tableName = db.SplitHelper(entity).GetTableNames();
var r2 = db.Updateable(entity).SplitTable(tas => tas.InTableNames(tableName)).ExecuteCommand();//通过表达式过滤出要更新的表
var r3 = db.Updateable(entity).SplitTable(tas => tas.Where(y => y.TableName.Contains("_20230901"))).ExecuteCommand();

SqlSugar操作文档
SqlSugar分表参照

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！