PostgreSQL MySQL 資料類型映射

postgresql , mysql , 類型映射

通常一家企業會有比較多的資料庫品種，最為常見的如mysql, postgresql。

那麼在不同的産品之間，如果有資料的互相同步，就涉及到類型的映射了。

對于postgresql來說，可以使用postgresql的mysql_fdw外部插件來實作同步，産品設計會簡化很多。

而對于mysql來說，可能就比較複雜，需要自己寫程式來實作，會涉及到類型的轉換。

<a href="https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html">https://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html</a>

postgresql type

mysql type

comment

int

-

smallint

bigint

serial

sets auto_increment in its table definition.

smallserial

bigserial

bit

boolean

tinyint(1)

real

float

double precision

double

numeric

decimal

money

decimal(19,2)

char

char/longtext

depending on its length. mysql server 5.5 and above can have char columns with a length up to 255 characters. anything larger is migrated as longtext

national character

depending on its length. mysql server 5.5 and above can have varchar columns with a length up to 65535 characters. anything larger is migrated to one of the text blob types. in mysql, character set of strings depend on the column character set instead of the datatype.

varchar

varchar/mediumtext/longtext

depending on its length. mysql server 5.5 and above can have varchar columns with a length up to 65535 characters. anything larger is migrated to one of the text blob types.

national character varying

date

time

timestamp

datetime

interval

bytea

longblob

text

longtext

cidr

varchar(43)

inet

macaddr

varchar(17)

uuid

varchar(36)

xml

json

tsvector

tsquery

array

point

line

linestring

although line length is infinite, and linestring is finite in mysql, it is approximated

lseg

a lseg is like a linestring with only two points

box

polygon

a box is a polygon with five points and right angles

path

circle

a polygon is used to approximate a circle

txid_snapshot

postgresql還有很多類型，上面并沒有列出來，比如圖像，化學，raster, 基因等。

取自mysql_fdw插件的代碼

<a href="http://api.pgxn.org/src/mysql_fdw/mysql_fdw-2.1.2/mysql_fdw.c">http://api.pgxn.org/src/mysql_fdw/mysql_fdw-2.1.2/mysql_fdw.c</a>

<a href="https://github.com/enterprisedb/mysql_fdw/blob/master/mysql_fdw.c">https://github.com/enterprisedb/mysql_fdw/blob/master/mysql_fdw.c</a>

類型映射如下

如果你要的不是資料同步，還有後續的動作，例如從一個産品遷移到另一個産品，那會是一個比較大的動作，是以多啰嗦幾句。

除了資料類型，還有sql文法，函數、索引方法(8種内置hash,btree,gin,gist,bloom,brin,rum,sp-gist)，也是遷移需要特别考慮的，postgresql在這方面支援更加全面，如果要從postgresql遷移到mysql需要特别注意。

列舉一些例子，取自如下文檔

<a href="https://github.com/digoal/blog/blob/master/201701/20170125_01_pdf_002.pdf">《資料庫選型的關鍵名額》</a>

1. 遞歸查詢, connect by, 樹形查詢

postgresql 通過(with 或 tablefunc支援)支援例子

<a href="https://yq.aliyun.com/articles/240">https://yq.aliyun.com/articles/240</a>

<a href="http://www.postgresql.org/docs/9.5/static/tablefunc.html">http://www.postgresql.org/docs/9.5/static/tablefunc.html</a>

2. 視窗查詢, window over

postgresql 支援例子

<a href="http://blog.163.com/digoal@126/blog/static/16387704020137154137930">http://blog.163.com/digoal@126/blog/static/16387704020137154137930</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/">http://blog.163.com/digoal@126/blog/static/16387704020121024102312302/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020124239390354/">http://blog.163.com/digoal@126/blog/static/16387704020124239390354/</a>

<a href="http://www.postgresql.org/docs/9.5/static/functions-window.html">http://www.postgresql.org/docs/9.5/static/functions-window.html</a>

3. rollup, grouping sets, cube

<a href="http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#queries-grouping-sets">http://www.postgresql.org/docs/9.5/static/queries-table-expressions.html#queries-grouping-sets</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020154269591874/">http://blog.163.com/digoal@126/blog/static/16387704020154269591874/</a>

4. 進階聚合(json,數組,相關性,标準差(采樣,全局),截距,斜率,方差(采樣,全局),mode,percentile_cont,distc,rank,dense_rank,percent_rank,cume_dist,grouping)

<a href="http://www.postgresql.org/docs/9.5/static/functions-aggregate.html">http://www.postgresql.org/docs/9.5/static/functions-aggregate.html</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402015224124337/">http://blog.163.com/digoal@126/blog/static/1638770402015224124337/</a>

<a href="http://blog.163.com/digoal@126/blog/static/1638770402015379286873/">http://blog.163.com/digoal@126/blog/static/1638770402015379286873/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020153713222764">http://blog.163.com/digoal@126/blog/static/16387704020153713222764</a>

4. hash join, merge join, nestloop join

<a href="http://www.postgresql.org/docs/9.5/static/xoper-optimization.html">http://www.postgresql.org/docs/9.5/static/xoper-optimization.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/planner-optimizer.html">http://www.postgresql.org/docs/9.5/static/planner-optimizer.html</a>

5. 哈希聚合

<a href="http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#runtime-config-query-enable">http://www.postgresql.org/docs/9.5/static/runtime-config-query.html#runtime-config-query-enable</a>

6. 事務間共享事務快照

<a href="http://www.postgresql.org/docs/9.5/static/functions-admin.html#functions-snapshot-synchronization">http://www.postgresql.org/docs/9.5/static/functions-admin.html#functions-snapshot-synchronization</a>

<a href="http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html">http://www.postgresql.org/docs/9.5/static/sql-set-transaction.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/app-pgdump.html">http://www.postgresql.org/docs/9.5/static/app-pgdump.html</a>

<a href="http://blog.163.com/digoal@126/blog/static/163877040201326829943/">http://blog.163.com/digoal@126/blog/static/163877040201326829943/</a>

<a href="http://blog.163.com/digoal@126/blog/static/163877040201241134721101/">http://blog.163.com/digoal@126/blog/static/163877040201241134721101/</a>

7. 展開式索引(支援多列任意組合查詢)

<a href="https://github.com/digoal/blog/blob/master/201702/20170205_01.md">《寶劍贈英雄 - 任意組合字段等效查詢, 探探postgresql多列展開式b樹》</a>

1. foreign key

<a href="http://www.postgresql.org/docs/9.5/static/ddl-constraints.html">http://www.postgresql.org/docs/9.5/static/ddl-constraints.html</a>

2. for no key update, for key share 粒度鎖

<a href="http://www.postgresql.org/docs/9.5/static/explicit-locking.html#locking-rows">http://www.postgresql.org/docs/9.5/static/explicit-locking.html#locking-rows</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020130249109133/">http://blog.163.com/digoal@126/blog/static/16387704020130249109133/</a>

<a href="http://blog.163.com/digoal@126/blog/static/16387704020130305109687/">http://blog.163.com/digoal@126/blog/static/16387704020130305109687/</a>

3. check 限制

4. exclusion 限制

1. 表空間

2. alter 列值轉表達式

(alter table alter column c1 type newtype using (expression(...)) )

<a href="http://www.postgresql.org/docs/9.5/static/sql-altertable.html">http://www.postgresql.org/docs/9.5/static/sql-altertable.html</a>

<a href="https://yq.aliyun.com/articles/30470">https://yq.aliyun.com/articles/30470</a>

3. alter table 需要重組表的操作

postgresql少量操作需要重組

vacuum full, cluster, 修改字段資料類型, (修改長度不需要重組表)

4. 分區表

<a href="https://github.com/digoal/blog/blob/master/201610/20161024_01.md">《postgresql 9.5+ 高效分區表實作 - pg_pathman》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161215_01.md">《postgresql 10.0 内置分區表》</a>

5. 物化視圖

<a href="http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html">http://www.postgresql.org/docs/9.5/static/sql-creatematerializedview.html</a>

6. 物化視圖增量重新整理

<a href="http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html">http://www.postgresql.org/docs/9.5/static/sql-refreshmaterializedview.html</a>

7. 表繼承關系

<a href="http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html">http://www.postgresql.org/docs/9.5/static/tutorial-inheritance.html</a>

8. 使用 like 建結構類似的表

<a href="http://www.postgresql.org/docs/9.5/static/sql-createtable.html">http://www.postgresql.org/docs/9.5/static/sql-createtable.html</a>

1. 用戶端開發語言支援

c, java, python, ...

2. 函數

傳回 void, 單行，srf，事件觸發器(mysql 不支援)，觸發器

例子

<a href="http://blog.163.com/digoal@126/blog/static/16387704020132131361949/">http://blog.163.com/digoal@126/blog/static/16387704020132131361949/</a>

<a href="http://www.postgresql.org/docs/9.5/static/event-triggers.html">http://www.postgresql.org/docs/9.5/static/event-triggers.html</a>

3. 2pc

4. 服務端綁定變量

<a href="http://www.postgresql.org/docs/9.5/static/sql-prepare.html">http://www.postgresql.org/docs/9.5/static/sql-prepare.html</a>

5. savepoint

6. 異步消息

<a href="http://www.postgresql.org/docs/9.5/static/sql-notify.html">http://www.postgresql.org/docs/9.5/static/sql-notify.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/sql-listen.html">http://www.postgresql.org/docs/9.5/static/sql-listen.html</a>

7. 遊标

數組for循環，query for循環，遊标for循環

postgresql 全面支援例子

<a href="http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html">http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html</a>

<a href="http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html">http://www.postgresql.org/docs/9.5/static/plpgsql-cursors.html</a>

(mysql 暫不支援數組)

1. 資料類型

1.1 postgresql

高精度numeric, 浮點, 自增序列，貨币，位元組流，時間，日期，時間戳，布爾，枚舉，平面幾何，立體幾何，多元幾何，地球，postgis，網絡，比特流，全文檢索，uuid，xml，json，數組，複合類型，域類型，範圍，樹類型，化學類型，基因序列，fdw, 大對象, 圖像

1.2 mysql

數字，時間，字元串，簡單的gis，json

2. 支援索引的資料類型

2.1 postgresql

高精度numeric, 浮點, 自增序列，貨币，位元組流，時間，日期，時間戳，布爾，枚舉，平面幾何，立體幾何，多元幾何，地球，postgis，網絡，比特流，全文檢索，uuid，xml，json，數組，複合類型，域類型，範圍，樹類型，化學，基因序列

2.2 mysql

數字，字元串，比特流，時間，全文檢索，gis

1. 索引方法

postgresql 支援 btree, hash, gist, sp-gist, gin, brin , bloom , rum 索引

mysql 支援 btree, gis類型索引

2. 規則表達式、前後模糊查詢，支援索引檢索

postgresql 支援

3. 數組支援索引

4. 全文檢索支援索引

5. 索引擴充功能

5.1 postgresql 支援 表達式索引，部分索引，聯合索引

5.2 postgresql 支援圖像相似度、文本相似度搜尋

5.3 自定義索引通路方法(bloom, rum, ......)

postgresql支援

1. 多引擎支援

内置heap, 通過插件實作記憶體表, 列存儲, 壓縮存儲, 流式存儲, 非關系存儲等。

myisam, innodb, ...

2. 外部表

postgresql支援任意外部資料源, (例如jdbc, file, odbc, oracle, mysql, db2, redis, mongo, es, hadoop......)

<a href="https://wiki.postgresql.org/wiki/fdw">https://wiki.postgresql.org/wiki/fdw</a>

3. dblink

4. 記憶體表

postgresql 通過外部表支援，或者通過插件支援

5. es(彈性搜尋引擎) 引擎

1. 資料加密

postgresql 支援加密資料類型，可選gpg加密算法

2. 認證方法

postgresql 支援 密碼、ldap、ad、gssapi、sspi、ident、peer、radius、pam、簽名認證

3. 資料傳輸加密

4. 行安全政策

5. 資料庫内部支援libselinux接口, (美國國家安全局制定的安全加強标準)

<a href="http://www.postgresql.org/docs/9.5/static/runtime-config-query.html">http://www.postgresql.org/docs/9.5/static/runtime-config-query.html</a>

1. gpu 并行計算支援

2. 遺傳優化器算法

postgresql 支援cbo、cro、遺傳算法

3. hint plan

4. cpu 并行計算

postgresql 支援 (線性性能提升)

5. 自定義成本因子

1. 是否支援采樣查詢

2. 是否支援擴充采樣算法

3. 自定義資料類型

4. 自定義索引方法

5. 字元集自動轉換, c擴充接口

6. 自定義聚合

7. 自定義視窗

1. 類型擴充，操作符擴充，函數擴充，索引方法擴充，索引擴充，

2. c觸發器函數， c事件觸發器函數

3. 函數語言擴充

postgresql 支援擴充函數支援接口, erlang, ....

4. 機器學習庫

5. 流式計算

<a href="https://github.com/digoal/blog/blob/master/201701/20170125_01.md">《資料庫界的華山論劍 tpc.org》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160911_01.md">《facebook linkbench 測試postgresql社交關系圖譜場景性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160927_01.md">《postgresql 主機性能測試方法 - 單機多執行個體》</a>

<a href="https://github.com/digoal/blog/blob/master/201609/20160927_02.md">《postgresql 主機性能測試方法 - 單機單執行個體》</a>

<a href="https://github.com/digoal/blog/blob/master/201611/20161129_01.md">《postgresql 源碼性能診斷(perf profiling)指南》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151215_01.md">《"物聯網"流式處理應用 - 用postgresql實時處理(萬億每天)》</a>

<a href="https://github.com/digoal/blog/blob/master/201601/20160119_01.md">《postgresql 百億地理位置資料 近鄰查詢性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201603/20160302_01.md">《postgresql 百億資料 秒級響應 正則及模糊查詢》</a>

<a href="https://github.com/digoal/blog/blob/master/201603/20160307_01.md">《postgresql 1000億資料量 正則比對 速度與激情》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161225_01.md">《恭迎萬億級營銷(圈人)潇灑的邁入毫秒時代 - 萬億user_tags級實時推薦系統資料庫設計》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161231_01.md">《從難纏的模糊查詢聊開 - postgresql獨門絕招之一 gin , gist , sp-gist , rum 索引原理與技術背景》</a>

<a href="https://github.com/digoal/blog/blob/master/201612/20161216_01.md">《分析加速引擎黑科技 - llvm、列存、多核并行、算子複用 大聯姻 - 一起來開啟postgresql的百寶箱》</a>

1. 函數(過程)語言

postgresql支援幾乎所有的地球程式設計語言，你可以用他們在資料庫中編寫udf

plpgsql, sql, c, c++, java, javascript, r, python, perl, php, tcl, ruby, lua, ...

1. postgresql完全支援以下下推功能

支援聚合算子下推

支援where條件下推

支援join下推

支援sort下推

支援select 子句下推

支援跨資料源join