MySQL至Hologres的数据迁移方法及语句、函数的区别 - 实时数仓Hologres

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

数据迁移方法

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

说明

如存在ETL处理场景，您可通过Flink读取MySQL数据再写入Hologres。

迁移类别	适用场景	使用文档
单表离线同步	适用于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 时，默认映射为 TIMESTAMPTZ（TIMESTAMP WITH TIME ZONE）。若需使用不带时区的 TIMESTAMP，请在同步配置或建表时单独指定。

MySQL中的数据类型	迁移至Hologres后对应的数据类型
BIGINT	BIGINT
BIGINT(20) UNSIGNED	TEXT
`BINARY(n)`	BYTEA
BIT	BOOLEAN
`CHAR(n)`、`CHARACTER(n)`	`CHAR(n)`、`CHARACTER(n)`
DATE	DATE
DATETIME	`TIMESTAMPTZ（TIMESTAMP WITH 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_id在tableA中是BIGINT类型，project_id在tableB中是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)