為什么要手搓一個自己的數據庫遷移工具

為什么要進行數據庫遷移？主要有這么幾種情況：

（1）、開發測試階段使用的數據庫類型于生產環境的數據庫類型不同，如開發測試用MySQL數據庫，生產環境用Oracle\SQLServer等企業級數據庫，需要將開發測試環境的一些基礎數據遷移到生產環境；為什么開發測試用的數據庫類型與生產環境不同，自然是為了降低開發成本，生產環境的數據庫是客戶購買的，軟件開發方未必能有條件購買使用同類型的數據庫產品。

（2）、開發測試環境使用的數據庫版本與生產環境的數據庫不同，例如開發測試環境使用的是數據庫的“開發版”、社區版，或者較低的版本，生產環境用的是企業版、最新的版本；這樣做自然也是為了降低開發成本，比如開發環境用金倉V8版本，而線上用的是最新的金倉V9版本。

（3）、國產化要求，需要將之前運行在MySQL、Oracle、SQLServer等數據庫上的數據遷移到金倉、達夢等國產數據庫上；國產化要求是現在很多政府類項目的硬性要求，軟件公司之前成熟的產品用的是非國產化數據庫，現在都有數據遷移需求。

（4）、提高數據庫性能和數據備份的要求；線上一些系統運行時間長，產生的很多歷史數據需要遷移到其它數據庫上以便給線上的數據庫“瘦身”，起到一個數據備份和提高線上系統查詢效率的作用；比如一些日志數據、報警數據，可以把歸檔后的數據遷移到新的數據庫上。

在國產數據庫領域，金倉數據庫算是比較常用的一種數據庫了，號稱百分百兼容Oracle的，也基本百分百兼容MySQL等數據庫，能“無縫”遷移。金倉等國產數據庫在兼容性這方面的確很努力，如果這事不努力國人為什么要用你的數據庫？在實際使用過程中的確感覺不到兼容性問題，但在做數據遷移的時候才發現在兼容性方面還是有一些問題，而金倉提供的數據遷移工具對于非資深用戶而言還是有較高的門檻，每次遇到問題都不得不求助于金倉的技術支持人員，每次尋求他們支持都要溝通很久，于是我決定自己手搓一個數據庫遷移工具，而且還能反向遷移，即從金倉遷移到MySQL等各種數據庫，而不是金倉自己的工具只能從Oracle，SQL Server,MySQL,PostgreSQL這幾種數據庫遷移到金倉。

關于數據遷移，以前工作中經常進行，因此積累了一定的經驗，而且還把這些經驗寫到《SOD框架“企業級”應用數據架構實戰》這本書里面了。下面介紹一下怎么使用SOD框架手搓一個數據遷移工具，開始之前必須先了解數據遷移有哪些問題，才能明白手搓一個自己的遷移工具的好處。

數據遷移的常見問題

數據遷移并不是一個簡單的工作，雖然很多數據庫都提供了將別的數據庫遷移過來的數據遷移工具，但使用的時候還是會遇到很多問題：

第一個問題是數據量很大，我們往往希望把一個有幾百萬行、幾千萬行的表數據從一個小型數據庫遷移到一個大中型的數據庫中，比如把一個單機MySQL數據庫中的數據遷移到金倉數據庫集群中；或者將上百萬的數據遷移到歷史數據庫中存檔給原來的庫瘦身。

第二個問題就是不同類型數據庫之間進行遷移，比如從MySQL遷移到金倉、從金倉遷移到Oracle，由于源數據庫和目標數據庫類型不同，它們支持的字段類型、表類型甚至SQL語法都有差異。有些數據庫廠商從商業上考慮可能只愿意提供將別的數據庫遷移到自家數據庫來的功能而不支持反向遷移。

第三個問題是同一種數據庫不同版本之間進行數據遷移。按理說數據庫不同版本之間應該保持兼容，至少是高版本兼容低版本的，但國產數據庫很牛，它不同版本之間的數據類型是可能不兼容的，導致數據無法直接遷移，這個問題讓我明白了金倉數據庫遷移工具為何使用起來那么復雜。

第四個問題是數據遷移過程可能還伴隨數據篩選、數據清洗和轉換，這個屬于ETL的范疇了，有一些成熟的ETL工具可以使用，但這些工具使用復雜并且不一定免費。

接下來我們自己手搓遷移工具時看怎么解決上面這些問題。

遷移方案設計

• 遷移的數據量很大，所以不能讀取太多的數據到內存，最好從源數據讀取一部分就寫入目標數據庫一部分數據；
• 不同種類數據庫之間進行數據遷移，由于數據庫之間SQL語法、字段類型有差異，所以最好不要直接采用編寫SQL語句的方式來實現，用ORM框架可以完美解決這個問題；
• 數據庫不同版本之間的數據遷移，注意采用兼容的數據類型即可，如果不能兼容也有辦法；
• 第四個問題好辦了，由于是自己手搓的工具，遷移前后可以自定義自己的處理邏輯，進行數據篩選、清洗和轉換工作都不在話下了。

根據這個遷移方案，采用SOD框架來實現是很合適的，它的一些特性解決這些問題具有很大優勢，下面我們逐個介紹怎么實現。

準備工作

首先我們需要明確源數據庫和目標數據庫的類型、版本，數據庫連接信息，要遷移的表和視圖數據。比如本文的例子以從金倉數據庫遷移到MySQL數據庫為例，使用VS先創建一個控制臺項目，目標框架選擇.NET8，然后項目中添加兩個Nuget包，在目文件中添加下面的包引用代碼：

 <PackageReference Include="PWMIS.SOD.Kingbase.Provider.Net6V9" Version="6.0.1" />
 <PackageReference Include="PWMIS.SOD.MySQL.Provider" Version="6.0.3" />

或者使用Nuget包管理工具，查找 PWMIS.SOD 關鍵字，然后安裝SOD框架的金倉數據庫訪問提供程序和MySQL數據庫訪問提供程序：

Install-Package PWMIS.SOD.Kingbase.Provider
Install-Package PWMIS.SOD.MySQL.Provider

然后在解決方案資源管理器選擇項目名稱，右鍵菜單“添加-新建項-常規”，然后選擇“應用程序配置文件”，添加一個 app.config文件，內容如下：

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <appSettings>
        <!--PDF.NET.SOD SQL 日志記錄配置（for 4.0）開始
        記錄執行的SQL語句，關閉此功能請將SaveCommandLog 設置為False，或者設置DataLogFile 為空；
        如果DataLogFile 的路徑中包括~符號，表示SQL日志路徑為當前Web應用程序的根目錄；
        如果DataLogFile 不為空且為有效的路徑，當系統執行SQL出現了錯誤，即使SaveCommandLog 設置為False，會且僅僅記錄出錯的這些SQL語句；
        如果DataLogFile 不為空且為有效的路徑，且SaveCommandLog 設置為True，則會記錄所有的SQL查詢。
        在正式生產環境中，如果不需要調試系統，請將SaveCommandLog 設置為False 。
    -->
        <add key="SaveCommandLog" value="True" />
        <add key="DataLogFile" value="Log\SqlLog.txt" />
        <!--LogExecutedTime 需要記錄的時間，如果該值等于0會記錄所有查詢，否則只記錄大于該時間的查詢。單位毫秒。-->
        <add key="LogExecutedTime" value="500" />
        <!--PDF.NET SQL 日志記錄配置 結束-->
    </appSettings>
    <connectionStrings>
    <add name="SourceDb"
         connectionString="Server=127.0.0.1;User Id=system;Password=system;Port=54321;Database=mydb;"
         providerName="PWMIS.DataProvider.Data.Kingbase,PWMIS.KingbaseClient.Net6V9" />
    <add name ="TargetDb"
         connectionString="server=127.0.0.1;User Id=root;password=123456;DataBase=mydb;"
         providerName="PWMIS.DataProvider.Data.MySQL,PWMIS.MySqlClient" />
    
    </connectionStrings>
 </configuration>

View Code

有關如何配置連接字符串的詳細內容，請移步框架的Nuget下載頁面：NuGet Gallery | PWMIS.SOD 6.0.3

注意金倉數據庫訪問程序的選擇不同版本有點差異，可以移步SOD的金倉數據庫Nuget下載頁面詳細了解：NuGet Gallery | PWMIS.SOD.Kingbase.Provider 6.0.7

到此使用SOD框架開發數據遷移工具的準備工作已經完成，下面正式開始編寫實現代碼。

 創建目標數據庫

 數據遷移通常都是目標數據庫已經存在的情況下進行的，但這里為什么要強調創建目標數據庫呢？這是因為很可能既有的目標數據庫的數據表和表字段與源數據庫是不兼容的，比如目標數據庫的字符編碼是UTF8，而源數據庫是GB2312，目標表的字段類型是int而源表字段的類型是long,或者表字段都是varchar類型但是源表和目標表該字段的長度卻不相同，當然更夸張的是連字段名都可能不相同（字段業務含義是一樣的），這些千奇百怪的問題只有你想不到沒有你遇不到的。所以最佳辦法是由遷移工具自動創建一個目標數據庫。

SOD框架的Code First方案可以由實體類創建表，它在第一次連接數據庫的時候檢查表是否存在，如果不存在才創建表，如果表已經存在則跳過以避免意外更改表結構。實現此過程很簡單，只需要繼承DbContext即可，比如對于本文的目標數據庫，創建一個TargetDbContext類：

public class TargetDbContext : DbContext
{
    public TargetDbContext () : base("TargetDb")
    {
      
    }

     protected override bool CheckAllTableExists()
     {
             CheckTableExists<UserInfo>();
             //創建其它表。。。
             return true;
    }
}

在上面的代碼中，DbContext類的構造函數參數值“TargetDb” 就是app.config中配置的連接名稱，重載方法CheckAllTableExists 中 CheckTableExists泛型方法的類型參數UserInfo是一個SOD實體類，它可以根據實體類指定的表名稱來創建目標表。這樣，當TargetDbContext類型的對象被實例化的時候就會自動創建好遷移數據的目標表了。

遷移標識字段

數據庫的標識字段是用來唯一標識一行數據的，主鍵就起到這種作用，我們也使用帶自增功能的字段做主鍵，但自增字段不一定都是主鍵，本文說的標識字段是數據庫的自增標識列，如SQLServer的IDENTITY 列，MySQL 用 AUTO_INCREMENT，Oracle 用 SEQUENCE+觸發器或 IDENTITY 列，PostgreSQL和金倉數據庫也是用 SEQUENCE 并設置 DEFAULT nextval。

SOD框架的Code First功能可以為各種數據庫自動創標識列，只需要實體類設置 Identity="標識字段名"即可，示例代碼如下：

public class UserInfo : EntityBase
{
  public UserInfo()
  {
          TableName = "UserInfo";
          IdentityName = "ID";    //標識字段
          PrimaryKeys.Add("ID"); //主鍵
  }

 public int ID
 {
     get{return getProperty<int>("ID");}
     set{setProperty("ID",value );}
 }

 public string Name
 {
     get{return getProperty<string>("Name");}
     set{setProperty("Name",value ,50);}
 }
}

默認情況下自增字段（IDENTITY / AUTO_INCREMENT / SERIAL）在插入數據的時候不能直接插入值，但在數據遷移的時候，需要將自增字段的值也遷移過去，除非自增字段沒有被別的表在邏輯上引用。如果確實需要給自增列塞一個指定值，必須顯式關閉/繞過自增字段的這個機制，操作完恢復自增字段的默認行為，否則后續普通方式插入數據會出錯。比如對于SQLServer數據庫：

-- 1 允許手動插入自增字段值
SET IDENTITY_INSERT dbo.UserInfo ON;
-- 2 手動寫值（列清單必須顯式寫出）
INSERT INTO dbo.UserInfo (ID Name) VALUES (100, 'zhangsan');
-- 3 恢復自增字段默認行為
SET IDENTITY_INSERT dbo.UserInfo OFF;

對于PostgreSQL和金倉數據庫的默認模式（PG模式）下，自增字段可以直接插入值，只要插入的值與現有自增字段值不重復即可。SOD框架根據實體類是否設置IdentityName屬性來決定插入數據的時候是否插入自增列的值，所以在使用SOD框架遷移數據的時候除了要注意目標數據庫對于自增字段的問題，還需要設置IdentityName屬性為空值，我們定義一個 IImportable 接口來表示該實體類可以插入自增字段值：

 public interface IImportable
 {
      void IgnoreIdentity();
 }

修改前面的實體類，將IdentityName設置為空：

public class UserInfo : EntityBase,IImportable
{
  public public_AlarmsInfo()
  {
          TableName = "UserInfo";
          IdentityName = "ID";     //標識字段
          PrimaryKeys.Add("ID"); //主鍵
  }

  public void IgnoreIdentity()
  {
      IdentityName = "";
  }

 public int ID
 {
     get{return getProperty<int>("ID");}
     set{setProperty("ID",value );}
 }

 public string Name
 {
     get{return getProperty<string>("Name");}
     set{setProperty("Name",value ,50);}
 }
}

采用這種方式在運行時修改IdentityName 屬性值，既可以享受到Code First自動創建目標表的便利，又可以實現插入自增列數據的功能；注意遷移完自增列數據后，需要重置自增列的標識數據到最大的自增列值，這樣后續插入數據才不會出問題。對于金倉數據庫遷移完成當前表的數據后，可以用下面的方式重置自增列的標識數據：

//entity 是當前正在遷移的表對于的SOD實體類對象
 if (targetDb.CurrentDBMSType == PWMIS.Common.DBMSType.Kingbase)
 {
     //更新序列值ALTER SEQUENCE equipment_id_seq RESTART WITH 100;
     string tableName = entity.GetTableName();
     string sql = $"ALTER SEQUENCE {tableName.ToLower()}_id_seq RESTART WITH {max_id + 1}";
     targetDb.ExecuteNonQuery(sql);
 }

另外一種可選方式是在數據庫上直接將原來的標識字段修改為普通字段，然后在實體類構造函數里面注釋掉 IdentityName 這行字段即可，但用這種方式來進行Code First模式開發無法自動創建自增列，但可以等數據遷移完成后再手動設置自增標識。

 大數據量查詢

 一次性在內存中加載10萬條數據很可能導致進程無法正常運行，而且這種大數據也會導致.NET內存難以有效回收，而大表數據遷移又是很常見的事情，所以最佳方案是數據逐條讀取，讀一部分寫一部分，避免將大量數據讀取到內存后再寫入，這樣可以加快遷移速度。SOD框架的實體類查詢支持這種“迭代器查詢”，通過調用EntityQuery<T>.QueryEnumerable方法：

static void DataMigration<T>(AdoHelper sourceDb,AdoHelper targetDb,Action<T> action, string identityName="ID") where T : EntityBase, new()
{
//其它代碼略
 var oql = OQL.From<T>().END;
 var readQuery = EntityQuery<T>.QueryEnumerable(oql, sourceDb);
 var insetQuery = new EntityQuery<T>(targetDb);
 foreach (var item in readQuery)
 {
    action(item);
    //寫入數據到目標數據庫，代碼暫略
 }
 //其它代碼略
}

QueryEnumerable 方法通過DataReader對象循環讀取數，每次只返回一個讀取的實體類對象，從而避免了一次讀取大量數據的問題。

 數據復制

 廣義的數據復制是將讀取的數據寫入到目標數據庫，但這里說的數據復制是將上面讀取的數據復制到一個新的對象里面。雖然理論上可以將從源數據庫讀取的實體類直接寫入到目標數據庫，但數據遷移的環境可能比較復雜，比如源數據庫和目標數據庫是不同類型的數據庫，或者雖然類型一樣但是版本不一樣，或者字段名稱不一樣甚至字段類型都不完全一樣；另外一個原因是SOD實體類的設計與市面上絕大部分ORM都不同，SOD實體類采用值數組的方式存儲從數據庫讀取的原始值，這些值可能攜帶了數據驅動程序特定的類型信息，而這種類型可能與目標數據庫的類型是不兼容的，比如日期類型，MySQl驅動程序有自己的日期子類型，金倉數據庫驅動程序也有自己的日期子類型，甚至不同版本的金倉數據庫日期子類型還有微小的差異，所以數據遷移的時候最好消除源數據庫讀取字段的特定的類型信息，直接使用.NET的數據類型，然后讓數據庫驅動程序根據.NET數據類型轉換到目標數據庫支持的數據類型。驅動程序數據類型轉換的問題比較復雜這里不細究。

針對不同的數據復制場景，SOD有不同的支持方案，最通常的方案是直接調用實體類的MapForm方法做數據映射拷貝：

 var insetQuery = new EntityQuery<T>(targetDb);
 foreach (var item in readQuery)
 {
    T targetEntity = new T();
    targetEntity.MapFrom(item,false);
    targetEntity.ResetChanges(true);
    //其它代碼略
  }

上面代碼中MapFrom方法表示從任意一個實例對象中拷貝與當前實體類同名屬性的值到當前實體類中，ResetChanges方法強行設置所有屬性的修改狀態是否修改，SOD框架會根據實體類屬性是否修改（是否進行過賦值操作）來決定是否將該屬性的值更新或者插入到數據庫中。除了調用上面的兩個方法，直接使用SOD的擴展方法CopyTo方法也可以實現類似的效果：

foreach (var item in readQuery)
{
  T targetEntity = new T();
  item.CopyTo(targetEntity);
}

但是使用上面的方式都沒法復制源數據庫表字段的NULL值，這個功能對SOD框架來說很簡單：

foreach (var item in readQuery)
{
  T targetEntity = new T();
  item.CopyTo(targetEntity);
  for (int i = 0; i < item.PropertyValues.Length; i++)
   {
    if (item[i] == DBNull.Value)
    {
        targetEntity.PropertyValues[i]= DBNull.Value;
    }
  }
}

上面的代碼中item是源數據庫的實體類對象，targetEntity是目標數據庫的實體類對象，SOD的實體類具有索引器功能，可以通過索引訪問屬性值，也可以通過索引直接修改實體類的屬性值，這樣就可以為屬性設置NULL值。實體類的這種訪問方式是絕大部分ORM框架都不支持的功能，這個功能為SOD框架處理數據帶來了極大的便利性。

 批量插入

 目標數據庫的寫入是數據遷移過程中最慢的操作，批量插入能大大提高插入操作的性能，很多數據庫都有一次性插入多條數據的功能，其中大多支持下面這種方式：

INSERT INTO 表名 (列1, 列2, …)
VALUES
    (值1_1, 值1_2, …),
    (值2_1, 值2_2, …),
    …
    (值n_1, 值n_2, …);

SOD框架對于MySQL和金倉數據庫采用了這種方式進行批量插入，對于SQLServer采用了SqlBulkCopy方案。只要支持批量插入，都可以調用QuickInsert方法：

InsertListData<T>(List<T> targetList, EntityQuery<T> insetQuery) where T : EntityBase, new()
{
 int importedCount=insetQuery.QuickInsert(targetList);
 return importedCount;
}

如果數據庫不支持批量插入，也可以使用EntityQuery對象的Insert重載方法插入一個實體列表對象，內部使用事務等方式優化插入性能。

進度信息

 數據遷移可能耗時比較長，遷移過程中實時顯示遷移進度是必要的，我實現了一個ConsoleProcessDisplayer類，效果類似Linux系統中下載文件的命令行進度顯示方式，下面直接給出主要代碼：

/// <summary>
/// 顯示處理進度
/// </summary>
/// <param name="readIndex">讀取的數據位置</param>
/// <param name="writeIndex">寫入的數據位置</param>
public void DisplayProcessing(int readIndex,int writeIndex=0)
{
    if(writeIndex==0) writeIndex = readIndex;
    
    if (readIndex >= recordCount)
    {
        Console.SetCursorPosition(this.left, this.top);
        var str = new string('=', 100);
        Console.Write("{0}[R:{1}/W:{2}/C:{3}]({4}%)", str, readIndex, writeIndex, recordCount, 100);
        return;
    }
    int currMSec = DateTime.Now.Millisecond / 100; //0.1秒的顯示間隔
    if(currMSec!=lastMSec)
    {
        lastMSec = currMSec;
        //顯示控制
        Console.SetCursorPosition(this.left, this.top);
        char[] w_arr = new char[screenWidth];
        for (int j = 0; j < screenWidth; j++)
        {
            w_arr[j] = '=';
        }
        int p = currentWidth > screenWidth ? currentWidth % screenWidth : currentWidth;
        w_arr[p - 1] = '>';
        if (p < screenWidth)
            w_arr[p] = '>';
        if (p+1 < screenWidth)
            w_arr[p+1] = ' ';
        string w_str = p >= screenWidth ? "" : new string(w_arr,0,p+1);
        double dpCount = writeIndex * 100 / dcCount;
        Console.ForegroundColor = ConsoleColor.Green;
        Console.Write(w_str);
        Console.ForegroundColor= ConsoleColor.White;
        string w_str2=p>= screenWidth? "": new string(w_arr,p+1,screenWidth-p-1);
        Console.Write("{0}>[{1}/{2}]({3}%)", w_str2, readIndex, recordCount, dpCount.ToString("f2"));
    }

    currentWidth = currentWidth <= screenWidth ? currentWidth + 1 : 1;
}

View Code

下面是模擬顯示進度的代碼調用方法：

int recordCount = 3721;
Console.WriteLine("【模擬】開始處理數據：");
ConsoleProcessDisplayer displayer = new ConsoleProcessDisplayer(recordCount);
Console.WriteLine("插入記錄數：");
displayer.Begin();
for (int i = 0;i< recordCount; i++)
{
    displayer.DisplayProcessing(i);
    System.Threading.Thread.Sleep(10);
}
displayer.DisplayProcessing(recordCount);
displayer.End(); 

至此已經介紹完成了使用SOD框架實現一個數據遷移工具的主要功能，其它就是一些容錯性處理和進度顯示控制，以及遷移N個表的方法重復調用，當然如果需要反復進行遷移測試，每次遷移之前可能還需要清理數據，調用DbContext類的TruncateTable泛型方法即可。

如果有朋友對本文的遷移方案感興趣，可以加群聯系我，聯系方式參考 http://www.pwmis.com/sqlmap