本文将为您介绍,MySQL数据平滑迁移至Hologres的操作方法,以及迁移完成后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中的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及以上版本,如您的版本低于该要求,可搜索(钉钉群号:32314975)加入实时数仓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参数不报错,若有需要请您使用自助升级或通过搜索(钉钉群号:32314975)加入实时数仓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的除法,需要显式做类型转换。
select 1/2::FLOAT;
- 问题描述:
- IF函数
Hologres不支持IF函数,需转换为CASE WHEN函数。
- IFNULL函数
MySQL的IFNULL函数,对应Hologres中的
COALESCE(x,y)
函数。 - LENGTH函数
MySQL中的LENGTH函数,对应Hologres中的
CHAR_LENGTH(string)
函数。