MySQL至Hologres的資料移轉方法及語句、函數的區別 - Hologres

本文將為您介紹，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 時，預設映射為 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參數不報錯，若有需要請您使用常見升級準備失敗報錯或加入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)