sqlsugar 分表

一、首字母分表

1. 安装 hyjiacan.pinyin4net

   > dotnet add package hyjiacan.pinyin4net --version 4.1.1
   

2. 创建分表服务

   /// <summary>
   /// Apricot 分表
   /// </summary>
   public class ApricotSplitTableService : ISplitTableService
   {
      /// <summary>
      /// sqlsugar 默认分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="EntityInfo"></param>
      /// <param name="tableInfos"></param>
      /// <returns></returns>
      public List<SplitTableInfo> GetAllTables(ISqlSugarClient db, EntityInfo EntityInfo, List<DbTableInfo> tableInfos)
      {
         List<SplitTableInfo> result = new List<SplitTableInfo>();
         foreach (var item in tableInfos)
         {
               if (item.Name.Contains("apricot")) //区分标识如果不用正则符复杂一些，防止找错表
               {
                  SplitTableInfo data = new SplitTableInfo()
                  {
                     TableName = item.Name //要用item.name不要写错了
                  };
                  result.Add(data);
               }
         }
         return result.OrderBy(it => it.TableName).ToList();//打断点看一下有没有查出所有分表
      }
   
      /// <summary>
      /// sqlsugar 默认分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="splitType"></param>
      /// <param name="entityValue"></param>
      /// <returns></returns>
      public object GetFieldValue(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object entityValue)
      {
         var column = entityInfo.Columns.FirstOrDefault(it => it.PropertyInfo.GetCustomAttribute<SplitFieldAttribute>() != null);
   
         var value = column.PropertyInfo.GetValue(entityValue, null);
   
         return value;
      }
   
      /// <summary>
      /// sqlsugar 默认分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo)
      {
         return entityInfo.DbTableName;
      }
   
      /// <summary>
      /// sqlsugar 默认分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="type"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType type)
      {
         return entityInfo.DbTableName;//目前模式少不需要分类(自带的有 日、周、月、季、年等进行区分)
      }
   
      /// <summary>
      /// 自定义分表
      /// </summary>
      /// <param name="db"></param>
      /// <param name="entityInfo"></param>
      /// <param name="splitType"></param>
      /// <param name="fieldValue"></param>
      /// <returns></returns>
      public string GetTableName(ISqlSugarClient db, EntityInfo entityInfo, SplitType splitType, object fieldValue)
      {
         return $"{entityInfo.DbTableName}_{GetPinyin($"{fieldValue}")}"; //根据值按首字母
      }
      
      /// <summary>
      /// 获取拼音首字母
      /// </summary>
      /// <param name="data"></param>
      /// <returns></returns>
      private static char GetPinyin(string data)
      {
         return Pinyin4Net.GetPinyin(data, PinyinFormat.None).FirstOrDefault();
      }
   }
   

3. 创建表实体

   // 指定自定义分表
   [SplitTable(splitType: SplitType._Custom01, CustomSplitTableService = typeof(ApricotSplitTableService))]
   public class Apricot
   {
      [SugarColumn(IsPrimaryKey = true)]
      public long Id { get; set; }
   
      // [SplitField] //标识一下分表字段
      public string Name { get; set; }
   
      // 分表字段、并且不存库
      [SplitField, SugarColumn(IsIgnore = true)]
      public string SplitName { get; set; }
   
      public DateTime CreateTime { get; set; }
   }
   

4. 写入数据 自动分表

   using var scope = app.Services.CreateScope();
   
   var context = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();
   
   var datas = new List<Apricot>()
      {
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="测试",
            SplitName ="测试"
         } ,
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="分表",
            SplitName ="分表"
         },
         new Apricot()
         {
            CreateTime = DateTime.Now,
            Name="数据",
            SplitName ="数据"
         }
      };
   
   List<long> ids = context.CopyNew().Insertable(datas).SplitTable().ExecuteReturnSnowflakeIdList();
   

5. 分表查询

   using var scope = app.Services.CreateScope();
   
   var context = scope.ServiceProvider.GetRequiredService<ISqlSugarClient>();
   
   var apricot = await context.Queryable<Apricot>().Where(p => p.Name == "测试").SplitTable().ToListAsync();
   

6. 参考
   • 官网