全部產品
Search
文件中心

Hologres:遷移MySQL至Hologres

更新時間:Jun 30, 2024

本文將為您介紹,MySQL資料平滑遷移至Hologres的操作方法,以及遷移完成後MySQL與Hologres查詢語句與函數的使用區別,方便您更加快速的完成資料移轉。

資料移轉方法

下表將根據您的遷移類別,為您介紹該類別適用的情境以及遷移方法。

說明

如存在ETL處理情境,您可通過Flink讀取MySQL資料再寫入Hologres。

遷移類別

適用情境

使用文檔

單表離線同步

適用於MySQL單表資料離線同步至Hologres的情境。

MySQL單表離線同步至Hologres

單表即時同步

通過開啟MySQL Binlog,將單表資料即時同步至Hologres。

MySQL單表即時同步至Hologres

整庫即時同步

將MySQL資料庫整庫即時同步至Hologres。

MySQL整庫即時同步至Hologres

同步解決方案

Data Integration支援同步解決方案功能,您可以通過配置同步規則,一次性即時同步資料至對應的資料來源中。

同步解決方案支援整庫內批量同步多張表,也支援全量、增量資料一體化同步(先同步全量資料,再即時同步增量資料)。

同步解決方案至Hologres

資料類型映射關係

您可參見下表,查看MySQL中的資料移轉至Hologres後對應的資料類型映射關係,更多資料類型請參見資料類型匯總

MySQL遷移至Hologres時,資料類型映射需注意如下事項:

  • Hologres中有3種整型(SMALLINT(2 Bytes)、INTEGER(4 Bytes)、BIGINT(8 Bytes)),而MySQL中有5種整型(TINYINT(1 Byte)、SMALLINT(2 Bytes)、MEDIUMINT(3 Bytes)、INT(4 Bytes)、BIGINT(8 Bytes)),此時您需選擇Bytes數更高的類型進行映射。

  • Hologres不支援無符號整型,在進行資料類型映射時需考慮無符號欄位造成的資料溢出,如超出對應欄位範圍則需考慮映射更大範圍的整型。

  • 您可使用Hologres的TEXT類型,替換MySQL中的TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT類型。

  • 浮點類型(DECIMAL、NUMERIC、DOUBLE、FLOAT)可直接映射。

  • MySQL中的DATETIME類型(不含時區資訊,格式為YYYY-MM-DD HH:MM:SS)對應Hologres中的TIMESTAMP類型(TIMESTAMP WITHOUT TIME ZONE)。

MySQL中的資料類型

遷移至Hologres後對應的資料類型

BIGINT

BIGINT

BIGINT(20) UNSIGNED

TEXT

BINARY(n)

BYTEA

BIT

BOOLEAN

CHAR(n)CHARACTER(n)

CHAR(n)CHARACTER(n)

DATE

DATE

DATETIME

TIMESTAMP [WITHOUT TIME ZONE]

DECIMAL(p,s)DEC(p,s)

DECIMAL(p,s)DEC(p,s)

DOUBLE

DOUBLE PRECISION

FLOAT

REAL

INT、INTEGER

INT、INTEGER

MEDIUMINT

INTEGER

NUMERIC(p,s)

NUMERIC(p,s)

SMALLINT

SMALLINT

TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

BYTEA

TINYINT

SMALLINT

TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

TEXT

TIME

TIME [WITHOUT TIME ZONE]

TIMESTAMP

TIMESTAMP [WITH TIME ZONE]

VARBINARY(n)VARBINARY(max)

BYTEA

VARCHAR(n)

VARCHAR(n)

VARCHAR(max)

TEXT

查詢文法

MySQL和Hologres的查詢文法在使用中有部分差異,具體內容如下。

  • 引號

    Hologres對大小寫不敏感,如需區分大小寫請添加英文雙引號("")。

    例如,將select `A` from b替換為select "A" from b

  • 條件式篩選

    條件式篩選時存在類型不符的情況,Hologres要求條件式篩選的類型必須完全符合且預設不做隱式類型轉換。具體樣本如下:

    • 範例程式碼:

      SELECT * FROM business_module WHERE ds = 20210329;
    • 問題描述:

      如果ds在Hologres表裡是TEXT類型,而20210329是INTEGER類型,則這個語句會直接提示類型不符的錯誤。錯誤提示如下。

      operator does not exist: text = integer;
    • 解決方案:

      Hologres支援建立自訂類型轉換,您可參見如下代碼建立轉換規格。

      CREATE CAST (TEXT  AS INTEGER) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS BIGINT) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS DECIMAL) WITH INOUT AS IMPLICIT; 
      CREATE CAST (TEXT  AS TIMESTAMP ) WITH INOUT AS IMPLICIT; 
      CREATE CAST (NUMERIC  AS TEXT ) WITH INOUT AS IMPLICIT;
  • 分頁

    MySQL中的分頁文法為limit 0,10,遷移至Hologres後的標準文法為offset 0 limit 10

  • 排序

    MySQL的排序行為是desc nulls first asc nulls first,而Hologres排序的預設行為是desc nulls first asc nulls last

    為保證使用體驗一致,請將Hologres查詢語句手動調整為order by XXX desc nulls last

  • 分組

    Hologres預設不支援FLOAT、DOUBLE等非精確類型的GROUP BY,您可將類型更改為DECIMAL類型,或通過如下參數進行配置。

    說明

    下述內容需要您的Hologres版本為0.10及以上版本,如您的版本低於該要求,可加入即時數倉Hologres交流群聯絡專業人員為您升級執行個體,詳情請參見如何擷取更多的線上支援?

    set hg_experimental_enable_double_equivalent=on;--session層級
    alter database XXX set hg_experimental_enable_double_equivalent=on;--整個庫生效
  • Union

    Union要求列的欄位類型必須完全符合。樣本如下。

    • 範例程式碼:

      SELECT project_id FROM tableA union ALL  select project_id from tableB;
    • 問題描述:

      project_idtableA中是BIGINT類型,project_idtableB中是TEXT類型。這類SQL在MySQL裡會做隱式轉換正常返回結果,在Hologres裡執行則會提示異常。異常語句如下。

      UNION types bigint and text cannot be matched;
    • 解決方案:

      Union操作需要顯式的做類型轉換。

      SELECT project_id FROM tableA union ALL  select cast(project_id as bigint) from tableB; 

函數使用

Hologres已相容PostgreSQL的大部分函數,詳情請參見PostgreSQL相容函數。MySQL和Hologres的函數在使用中有部分差異,具體內容如下。

  • 除數為0

    • 問題描述:

      MySQL裡除數為0時會返回NULL值,而在Hologres中會提示如下錯誤。

      ERROR: division by zero;
    • 解決方案:

      • select a/ b from table;
        轉換為 select a/ NULLIF(b,0) from table;
      • Hologres從V1.3.21及以上版本開始,當除以0時,可以使用以下GUC參數不報錯,若有需要請您使用自助升級或加入HologresDingTalk交流群反饋,詳情請參見如何擷取更多的線上支援?

        --建立MySQL相容性外掛程式,需要Superuser執行,一個資料庫只需要執行一次即可
        create extension if not exists mysql_compatible;
        --設定除以0開關(開啟後允許DQL除零容忍)
        set mysql_compatible.enable = on;

        使用樣本如下。

        --建立MySQL相容性外掛程式,需要Superuser執行,一個資料庫只需要執行一次即可
        create extension if not exists mysql_compatible;
        
        --情境1:同類型常量除
        set mysql_compatible.enable = on;
        select 1/0;
        
        
        --情境2:帶類型轉換常量除
        set mysql_compatible.enable = on;
        select 1.0/0;
        
        --情境3:被除數為變數列
        set mysql_compatible.enable = on;
        select sum(c) / 0 from (select generate_series(1,100) as c) as t;
        
        
        --情境4:除數為變數列
        set mysql_compatible.enable = on;
        select max(c)/sum(d) from (select generate_series(1,101) as c, generate_series(-50,50) as d) as t;
        
        --情境5:insert時容忍除以0
        create table if not exists test_insert_divide_by_zero(c1 int);
        set mysql_compatible.strict_mode = off;
        set mysql_compatible.enable = on;
        insert into test_insert_divide_by_zero select 100 / 0.0;
  • 整數相除

    • 問題描述:

      兩數相除有餘數時,MySQL會返回小數點,而Hologres會返回整數捨棄餘數。

      例如,5除以2,MySQL會返回2.5,而Hologres會返回2。

    • 解決方案:

      如果需要相容MySQL的除法,需要顯式做類型轉換。

      select1/2::FLOAT;
  • IF函數

    Hologres不支援IF函數,需轉換為CASE WHEN函數。

  • IFNULL函數

    MySQL的IFNULL函數,對應Hologres中的COALESCE(x,y)函數。

  • LENGTH函數

    MySQL中的LENGTH函數,對應Hologres中的CHAR_LENGTH(string)函數。

常見問題

MySQL和Hologres的COUNT DISTINCT多列計算結果不一致

  • 問題原因

    MySQL中,使用count(distinct column_1, column_2, ...)做多列去重計算時,如果某列有值為NULL,則該行的DISTINCT結果為NULL,不計入COUNT統計。

    Hologres中,使用count(distinct(column_1, column_2, ...))做多列去重計算時,某欄位值為NULL不影響計算結果,會計入COUNT統計。

  • 解決方案

    如果想要Hologres的計算結果與MySQL保持一致,則需要將Hologres中的查詢語句改為count(distinct column_1 || column_2 || ...)

  • 使用樣本

    CREATE TABLE count_distinct_test (
      a text,
      b text
    );
    
    INSERT INTO count_distinct_test VALUES ('a', 'b'), ('a', NULL), (NULL, 'b'), ('a', 'b');
    
    -- Hologres中做count distinct多列計算
    SELECT count(distinct(a, b)::text) FROM count_distinct_test;
    
    -- 返回結果
     count 
    -------
         3
    (1 row)
    
    -- Hologres中實現與MySQL結果一致的count distinct多列計算
    SELECT count(distinct a||b) FROM count_distinct_test;
    
    -- 返回結果
     count 
    -------
         1
    (1 row)