文档

迁移MySQL至Hologres

更新时间:

本文将为您介绍,MySQL数据平滑迁移至Hologres的操作方法,以及迁移完成后MySQL与Hologres查询语句与函数的使用区别,方便您更加快速的完成数据迁移。

数据迁移方法

下表将根据您的迁移类别,为您介绍该类别适用的场景以及迁移方法。

说明

如存在ETL处理场景,您可通过Flink读取MySQL数据再写入Hologres,详情请参见Flink全托管

迁移类别

适用场景

使用文档

单表离线同步

适用于MySQL单表数据离线同步至Hologres的场景。

MySQL单表离线同步至Hologres

单表实时同步

通过开启MySQL Binlog,将单表数据实时同步至Hologres。

MySQL单表实时同步至Hologres

整库实时同步

将MySQL数据库整库实时同步至Hologres。

MySQL整库实时同步至Hologres

同步解决方案

数据集成支持同步解决方案功能,您可以通过配置同步规则,一次性实时同步数据至对应的数据源中。

同步解决方案支持整库内批量同步多张表,也支持全量、增量数据一体化同步(先同步全量数据,再实时同步增量数据)。

同步解决方案至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参数不报错,若有需要请您使用自助升级或加入Hologres钉钉交流群反馈,详情请参见如何获取更多的在线支持?

        --创建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)