通过 NewLife.XCode 迁移任意现有数据库到任意数据库（附分表分库方法）

• XCode 自带分表分库功能，而且很方便，通过自行指定目标数据库连接字符串，以及不同表名，可将数据保存到目标数据库，甚至不同分表

 /// 

 /// 通过实体操作接口批量拷贝数据库，也可作为分表分库功能使用
 /// 
 /// 
 /// 
 /// 
 /// 
 public static void CopyDataTable(IEntityOperate factory, string originConnName, string desConnName, int perCount = -1)
 {
     //分页获取数据，并更新到新的数据库，通过更改数据库连接来完成

     FixColumns(factory.Table.DataTable.Columns);

     var allCount = factory.Count;
     var tableName = factory.TableName;

     if (perCount < 0) perCount = GetDataRowsPerConvert(allCount);
     var pages = (int)Math.Ceiling((double)((double)allCount / (double)perCount));

     for (var i = 0; i < pages; i++)
     {
  // 与上面代码等效
  factory.ConnName = originConnName;
  // factory.TableName = tableName;

  var modelList = factory.FindAll(string.Empty, string.Empty, string.Empty, i * perCount, perCount);
  factory.ConnName = desConnName;

  // 分表分库的时候，这里可加上判断是否分表的逻辑，即可将数据保存到分表
  // factory.TableName = tableName + DateTime.Now.ToString("yyyy-MM-dd");

  modelList.Insert(true);
     }
 }

 public static void ExcCopy(string originConnName, string desConnName, int perCount = -1)
 {
     var asm = typeof(ApiClaims).Assembly;
     var entityTypes = asm.GetTypes().Where(s => typeof(Entitybase).IsAssignableFrom(s)).ToArray();

     foreach (var entityType in entityTypes)
     {
  var factory = entityType.AsFactory();
  CopyDataTable(factory, originConnName, desConnName, perCount);
     }
 }

 /// 

 /// 兼容处理，类型转换
 /// 
 /// 
 public static void FixColumns(List columns)
 {
     if (columns.Count < 1)
     {
  return;
     }

     var indexColumns = columns[0].Table.Indexes.SelectMany(s => s.Columns).ToArray();
     foreach (var column in columns)
     {
  // 主键、唯一、类型为text的全部修改为varchar(50)
  if ((!column.PrimaryKey &&
!indexColumns.Any(a => a.Equals(column.Name, StringComparison.OrdinalIgnoreCase))) ||
      !column.RawType.Contains("text", StringComparison.OrdinalIgnoreCase)) continue;

  column.RawType = "varchar(50)";
  column.Length = 50;
     }
 }

 public static void CheckColumnName(List columns)
 {
     foreach (var dataTableColumn in columns)
     {
  if (dataTableColumn.Name.IsNullOrWhiteSpace())
  {
      dataTableColumn.Name = dataTableColumn.ColumnName;
  }
     }
 }

 /// 

 /// 根据数据表的记录总数来设置一个合理的每次转换数目。数据量大，一次性导出导入不合理
 /// 
 /// 数据表记录总数
 /// 每次转换的记录数
 private static int GetDataRowsPerConvert(int allCount)
 {
     if (allCount < 1000) return 200;
     else if (allCount < 5000) return 500;
     else if (allCount < 50000) return 1000;
     else return 1500;
 }

0:53:55.258  1 N - Select * From ClientScopes limit 10000
10:53:55.258  1 N - 同步[ClientScopes/identityServer_sqlite]数据 0 + 3
10:53:55.270 27 N 19 Insert Into ClientScopes(Id,Scope,ClientId) Values(1,'admin_ui_profile',1),(2,'admin_api',1),(3,'openid',1)
10:53:55.272  1 N - 同步[ClientScopes/identityServer_sqlite]完成，共[3]行，耗时14ms，速度209tps

12:27:33.896  1 N - ClientScopes.Count 快速计算表记录数（非精确）[ClientScopes/identityServer_sqlite] 参考值 -2
12:27:33.896  1 N - Select * From ClientScopes Order By Id Desc limit 1
12:27:33.896  1 N - Select Count(*) From ClientScopes
12:27:33.897  1 N - Select * From ClientScopes Order By Id Desc limit 200
12:27:33.898  9 Y 33 SHOW TABLE STATUS FROM `IdentityServer`
12:27:33.899  9 Y 33 创建表：ClientScopes()
12:27:33.899  9 Y 33 Create Table If Not Exists ClientScopes(
	Id INT NOT NULL AUTO_INCREMENT,
	Scope TEXT NOT NULL,
	ClientId BIGINT NOT NULL,
	Primary Key (Id)
) DEFAULT CHARSET=utf8mb4;
12:27:33.902  9 Y 33 Create Index IX_ClientScopes_ClientId On ClientScopes (ClientId)
12:27:33.913  1 N - Insert Into ClientScopes(Id,Scope,ClientId) Values(3,'openid',1),(2,'admin_api',1),(1,'admin_ui_profile',1)