本文將為您介紹,MySQL資料平滑遷移至Hologres的操作方法,以及遷移完成後MySQL與Hologres查詢語句與函數的使用區別,方便您更加快速的完成資料移轉。
資料移轉方法
下表將根據您的遷移類別,為您介紹該類別適用的情境以及遷移方法。
如存在ETL處理情境,您可通過Flink讀取MySQL資料再寫入Hologres。
遷移類別 | 適用情境 | 使用文檔 |
單表離線同步 | 適用於MySQL單表資料離線同步至Hologres的情境。 | |
單表即時同步 | 通過開啟MySQL Binlog,將單表資料即時同步至Hologres。 | |
整庫即時同步 | 將MySQL資料庫整庫即時同步至Hologres。 | |
同步解決方案 | Data Integration支援同步解決方案功能,您可以通過配置同步規則,一次性即時同步資料至對應的資料來源中。 同步解決方案支援整庫內批量同步多張表,也支援全量、增量資料一體化同步(先同步全量資料,再即時同步增量資料)。 |
資料類型映射關係
您可參見下表,查看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 |
| BYTEA |
BIT | BOOLEAN |
|
|
DATE | DATE |
DATETIME |
|
|
|
DOUBLE | DOUBLE PRECISION |
FLOAT | REAL |
INT、INTEGER | INT、INTEGER |
MEDIUMINT | INTEGER |
|
|
SMALLINT | SMALLINT |
TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB | BYTEA |
TINYINT | SMALLINT |
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT | TEXT |
TIME |
|
TIMESTAMP |
|
| BYTEA |
|
|
| 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)