PostgreSQL使用MySQL作為外部表(mysql_fdw)

PostgreSQL 提供了一種存取和操作外部資料來源的機制，稱為外部封包裝器（Foreign Data Wrapper）。利用這種外部資料管理機制，我們可以在 PostgreSQL 中存取各種同構資料庫（PostgreSQL）、異構資料庫（MySQL、Oracle、SQL Server、SQLite、HBase、Cassandra、ClickHouse、CouchDB、MongoDB、Neo4j、Redis、Hadoop、HIve等）以及文字檔案（CSV、JSON、XML等）。

本文我們要介紹的就是連線 MySQL 資料庫的外部封包裝器：mysql_fdw。mysql_fdw 提供了讀寫 MySQL 外部表、連線池、WHERE 條件下推、返回欄位下推、預編譯語句、JOIN 下推、聚合函數（min、max、sum、avg、count）下推、ORDER BY 下推以及 LIMIT OFFSET 下推等功能。

下載安裝

點選 GitHub 下載 mysql_fdw 原始碼。

編譯 mysql_fdw 之前，需要安裝 MySQL C 語言使用者端庫。這個程式庫可以從 MySQL 官方網站下載。

對於 POSIX 相容系統，執行 make 進行編譯時需要確保 pg_config 程式位於 path 環境變數中。該程式通常位於 PostgreSQL 安裝目錄下的 bin 目錄中，例如：

$ export PATH=/usr/local/pgsql/bin/:$PATH

另外，mysql_config 程式頁需要位於 path 環境變數中：

$ export PATH=/usr/local/mysql/bin/:$PATH

然後，使用 make 命令編譯程式：

$ make USE_PGXS=1

最後，安裝 mysql_fdw：

$ make USE_PGXS=1 install

執行迴歸測試：

$ make USE_PGXS=1 installcheck

注意確保 MYSQL_HOST、MYSQL_PORT、MYSQL_USER_NAME 以及 MYSQL_PWD 環境變數的設定。預設設定可以參考 mysql_init.sh 指令碼。

使用範例

以下是一個簡單的使用範例，所有命令在 PoatgreSQL 中執行：

-- 載入擴充套件外掛
CREATE EXTENSION mysql_fdw;

-- 建立伺服器物件
CREATE SERVER mysql_server
	FOREIGN DATA WRAPPER mysql_fdw
	OPTIONS (host '127.0.0.1', port '3306');

-- 建立使用者對映
CREATE USER MAPPING FOR postgres
	SERVER mysql_server
	OPTIONS (username 'foo', password 'bar');

-- 建立外部表
CREATE FOREIGN TABLE warehouse
	(
		warehouse_id int,
		warehouse_name text,
		warehouse_created timestamp
	)
	SERVER mysql_server
	OPTIONS (dbname 'db', table_name 'warehouse');

-- 插入資料
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);

-- 查詢資料
SELECT * FROM warehouse ORDER BY 1;

warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
           1 | UPS            | 10-JUL-20 00:00:00
           2 | TV             | 10-JUL-20 00:00:00
           3 | Table          | 10-JUL-20 00:00:00

-- 刪除資料
DELETE FROM warehouse where warehouse_id = 3;

-- 更新資料
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;

-- 檢視執行計劃
EXPLAIN VERBOSE SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_name LIKE 'TV' limit 1;

                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit  (cost=10.00..11.00 rows=1 width=36)
	Output: warehouse_id, warehouse_name
	->  Foreign Scan on public.warehouse  (cost=10.00..1010.00 rows=1000 width=36)
		Output: warehouse_id, warehouse_name
		Local server startup cost: 10
		Remote query: SELECT `warehouse_id`, `warehouse_name` FROM `db`.`warehouse` WHERE ((`warehouse_name` LIKE BINARY 'TV'))

設定引數

以下引數用於 MySQL 外部伺服器物件：

• host：MySQL 伺服器的地址或者主機名，預設為 127.0.0.1；
• port：MySQL 伺服器的埠，預設為 3306；
• secure_auth：啟用或者禁用安全認證，預設為 true；
• init_command：連線 MySQL 伺服器之後執行的初始 SQL 語句；
• use_remote_estimate：是否執行遠端 EXPLAIN 命令獲取成本評估，預設為 false；
• reconnect：啟用或者禁用自動重新連線功能，預設為 false；
• sql_mode：設定 MySQL sql_mode 變數，預設為 ANSI_QUOTES；
• ssl_key：使用者端私鑰檔名；
• ssl_cert：使用者端公鑰認證檔名；
• ssl_ca：證書授權（CA）認證檔名。如果使用該選項，必須和 MySQL 伺服器的證書一致；
• ssl_capath：包含可信 SSL CA 認證檔案的目錄；
• ssl_cipher：SSL 加密允許的密碼列表；
• fetch_size：指定每次讀取的資料行數。該引數可以基於外部表或者外部伺服器進行指定，基於外部表指定的引數優先順序更高。預設為 100；
• character_set：MySQL 連線使用的字元集。預設為 auto，表示基於作業系統設定進行自動設定。在引入該選項之前，字元集設定為 PostgreSQL 資料庫字元集. To get this 。如果想要相容歷史設定，可以將 character_set 設定為特殊值 PGDatabaseEncoding。

以下引數用於 MySQL 外部表物件：

• dbname：MySQL 資料庫名，這是一個必填項；
• table_name：MySQL 表名，預設與 PostgreSQL 外部表同名；
• max_blob_size：不會被截斷讀取的最大 blob 大小；
• fetch_size：與外部伺服器物件的 fetch_size 引數相同。

以下引數用於建立使用者對映：

• username：連線 MySQL 伺服器的使用者名稱；
• password：連線 MySQL 伺服器的密碼。

以下引數用於 IMPORT FOREIGN SCHEMA 命令：

• import_default：匯入外部表定義時是否包含欄位的 DEFAULT 屬性，預設為 false；
• import_not_null：匯入外部表定義時是否包含欄位的 NOT NULL 約束，預設為 true；
• import_enum_as_text：匯入外部表定義時將 MySQL ENUM 型別對映為 PostgreSQL TEXT 型別，或者建立一個新的列舉型別並提示警告，預設為 false；
• import_generated：匯入外部表定義時是否包含欄位的 GENERATED 表示式，預設為 true。如果生成列表示式中使用了 PostgreSQL 中不存在的函數或者操作符，IMPORT 將會失敗。

到此這篇關於PostgreSQL使用MySQL作為外部表（mysql_fdw）的文章就介紹到這了,更多相關PostgreSQL使用MySQL外表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！